Hi everyone,

When I build an access plan for a SQL query, it does not use an index created even I run RUNSTATS on that table. When I check the sort operation on the access plan, it states that the number of rows in the table is 1,345,227. But from Control Center "Estimate Size...", it shows the table contains 12,107,043. Even I try couple times the access plan, it keeps use the old number and does not use the index and keeps sorting the table. What's problem with the DB2 access plan?

Thanks,

I am not clear on what you are asking. Is the problem that DB2 is not using an index that you think it should use, or is there a question about the number of rows in the table?

The number of rows sorted (or estimated to be sorted in the explain) is not necesarily the number of rows in the table. Nevertheless, it would be very unusual for DB2 to not use an index on a table with 1,345,227 rows if it would use an index on the same table (and same SQL) with 12,107,043 rows.

If you want help figuring out why an index is not used, please post the DDL of the table and all indexes (the complete DDL using db2look), and please post the exact SQL satatement in question.

Thanks,
There are four tables in the query. The access plan uses the other two indexes created (much smaller tables), but not the index for the big table. Even the query executing time dropes to 3 minutes from 4, the estimated cost becomes 2,199,096 from 2,036,601 after creating three new indexes (two were used in the access plan).

Hi.

I shouldn't be asking this but did you do a runstats after you created the index. Try also doing a reorgchk and see what it suggests.

Please go ahead and try using the db2advis utility and I am sure you will have a good idea of what to do ahead.

Sometimes db2 optimizer finds it relatively cheaper to avoid the index depending on the algorithm it generates using certain internal routines. What I suggest is that you try setting the optimization class for the specific query and see if you get better results.

These are just some suggestions which may help.

Is this a DPF (aka EEE) environment?
If so, there's a logical explanation why the stats don't seem to jive. Since UDB assumes equal distribution amongst the nodes/database partitions, so if there's data skewing amongst the data partitions, it will be take the number of rows located in the node which it's executing the RUNSTATS and multiply that number by the # of data partitions for the table's (tablespace's) database partition group.
There is a VERY recent IBM article located at:
http://www-106.ibm.com/developerwork...pay/index.html
detailing the ins-and-outs of RUNSTATS.

In the above scenario, should RUNSTATS execute against the partition where, due to data skewing, there are 0 rows for the given table, CARD would show 0 even if the table has millions of rows.

HTH,
Ruby

If you post the exact DDL and SQL as I asked, I am sure that the problem can be explained or corrected. If you don't post the information requested, then everything is pure speculation and is usually a waste of time.

Thanks for every one who responsing.

I run db2advis, and get the following result:

execution started at timestamp 2004-12-20-14.34.50.453926
found [1] SQL statements from the input file

Calculating initial cost (without recommmended indexes) [2,199,096.000000] timerons
Initial set of proposed indexes is ready.
Found maximum set of [5] recommended indexes
Cost of workload with all indexes included [2,196,167.250000] timerons
total disk space needed for initial set [ 16.614] MB
total disk space constrained to [ -1.000] MB
excluding index WIZ1. diskspace required now is [ 11.109] MB
3 indexes in current solution
[2199096.0000] timerons (without indexes)
[2196721.2500] timerons (with current solution)
[%0.11] improvement

Trying variations of the solution set.
Maximum number of variations reached.
--
-- execution finished at timestamp 2004-12-20-14.34.51.454562

All the indexes recommeded by db2advis does not include the one on the big table. Even I drop this index, it does not affect the query's performance. So I guess this index is useless to the query. And no more index is needed for this query because even creating the four recommended, the performance increased is little.

Thanks again and have a safe and happy holiday season,