StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you haven't done so already, try submitting the query to DB2's EXPLAIN utility to determine what the full access path is and which parts of the query are the most expensive. Any part of the explain plan that uses a relation scan (full table scan) to find rows is the most likely to be improved by an index.</p> <p>Before you add a bunch of indexes, make sure the tables and indexes involved have have accurate statistics for the optimizer to use. If the table has grown substantially since RUNSTATS was last run, the optimizer may be disregarding perfectly good indexes because it doesn't understand how large the tables have grown. Execute a new RUNSTATS if the cardinality and distribution of the data has changed significantly from what was captured during the last RUNSTATS.</p> <p>Posting the list of indexes that are already defined on the tables, along with the approximate number of rows in each table would help a lot.</p> <p>A LIKE search does not necessarily force a table scan, but it certainly can result in an index scan if the column specified is indexed. The EXPLAIN utility will show you what is actually happening in those situations.</p> <p>A foreign key does not always benefit from an index, especially for foreign keys that have very low cardinality throughout the table. Another issue is that the optimizer generally has to select the best index to use, so having a lot of sub-optimal indexes laying around will eventually slow down updates and may not accelerate reads all that much. </p> <p>Let's assume that no good indexes exist yet on these tables. From the limited information provided, an index built on ( CWARCASNBR, CIPTYSQNBR, CT1FYA ) for table CMSDPR23.JE215000 may reduce the expense of the join from CMSDPL23.JE026001. Similarly, there is hopefully an index already built on (ADDRESSID, ADDIDSEQNO ) for CMSDPR23.CT007000, since that smells like a primary key or at least a unique candidate key.</p> <p>Your ORDER BY is going to require a sort if a significant number of rows are returned. You may have a cheaper sort if you go after the same columns CP.JEBOA, CP.CT1FYA in the outer table, since it's only going to be scanned once.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload