Q and A

Asked and Answered

I have a large table that needs to be available at all times. I use a staging table for data loading, then I INSERT from the staging table into the large table. However; there are at least three other non-partitioned indexes (NPIs), which makes inserting to the large table time consuming. How would you deal with NPIs in this situation?

Robert Catterall responds:

A couple of thoughts come to mind. First, there is the new (with DB2 for OS/390 Version 7) option of executing a LOAD RESUME with SHRLEVEL CHANGE, allowing read/write data access by programs to occur while the new data is being added to the table. I do not have performance numbers, but I expect that, in terms of efficiency and elapsed time, online LOAD RESUME would come in somewhere between traditional "offline" LOAD and SQL INSERT.

You could also think about getting rid of some or all of the NPIs on the table. One way to do that is to replace an index with a table. Suppose, for example, that you have an NPI on column ABC in your table (I'll call it BIG_TABLE), and that the partitioning index is column XYZ. Conceivably, you could have a table with columns ABC and XYZ (I'll call this one XREF_TABLE). You get rid of the NPI on column ABC of BIG_TABLE. If a query that targets BIG_TABLE has a predicate referencing column ABC, you change the query to be a join of BIG_TABLE and XREF_TABLE. Will the query be more CPU-intensive, and will it take longer to execute? Probably, but a two- or three-way join needn't be a big hog if the tables involved have indexes that are a good match for the predicates.

Of course, if your INSERT job for BIG_TABLE now has to insert rows into XREF_TABLE as well, the added cost may cancel out the savings attributable to the removal of the NPI on column ABC defined on BIG_TABLE.

See a complete archive of reader/author Q&As .