DB2 not picking up right index for right query - detail below -Pl help 1

[Db2curiosity]

Hi,
we are doing the 2 following updates in DB2 8.1,the index meant for 1st update is getting used in 2nd update and making it very slow.

** Table c has 7MM rows.
** Table w has 300K records.
** RUN STATS are Current for both tables.
** REORGCHK on table c has been done and it looks ok

/** For Query1 we have a nonunique comp index INDEX1 on source,key1,key3,key9,col4 which is making it run very Fast**/

UPDATE Table1 c
SET c.col1 = 1
,c.COL2 = 2
,c.COL3 = CURRENT_TIMESTAMP
WHERE c.COL4 = 4
AND EXISTS (SELECT 1 FROM temp_table w
WHERE w.source = c.source
and c.source = 'SIEBEL'
and c.key1 = w.key1
and c.key3 = w.key3
and c.key9 = w.key9 )

/** For Query2 we have a nonunique comp index INDEX2 on source,key1,key2,key4,key6,col4 ***/

But the following query is not picking INDEX2( as per db2expln) , rather its picking up INDEX1 above running forever.
Interesting is that when i drop INDEX1 , the Query2 picks up INDEX2 and runs very fast ...

But that's not a solution because when i drop INDEX1 the Query1 becomes slow which is natural.

UPDATE Table1 c
SET c.col1 = 1
,c.COL2 = 2
,c.COL3 = CURRENT_TIMESTAMP
WHERE c.COL4 = 4
AND EXISTS (SELECT 1 FROM temp_table w
WHERE w.source = c.source
and c.source = 'SIEBEL'
and c.key1 = w.key1
and c.key2 = w.key2
and c.key4 = w.key4
and c.key6 = w.key6 )

** I tried building a single index INDEX3 which can take care both queries
(source,key1,key2,key3,key4,key6,key9,col4)
and dropped both INDEX1 and INDEX2 but that's not helping either.

** DB2 doesn't support SQL HINT like Oracle does so that i can tell the 2nd query to use INDEX2 instead of INDEX1 because that's for someone else.

Please help... I'm stuck in a very wierd situation.

 

[BrianTyler]

I would suggest that you try Selecting the Index columns, instead of value '1'. This may improve things as the optimizer should then know that the base table is not required to be accessed, so could use the more appropriate index.

Good luck
Brian

 

[gregsimpson]

DB2Curiosiy,

try running it through a db2advis session and see what db2 suggests.

Cheers
Greg

 

[rbod]

How did you update the statistics? When you did the runstats, what options did you use?

 

[Db2curiosity]

Ans1 ##
We dont have DB2ADVIS tool but i tried all the different combinations of cols in the INDEX ,but the QUERY2 is just very much adamant to use only INDEX1 (1,3,9) and running forever...

If only i drop INDEX1, its using INDEX2( or others) and running fast as its expected to do.

Ans2 ##
Generally we are using the following syntax of RUNSTATS....

RUNSTATS ON TABLE db2dba.XXXXXXXX WITH DISTRIBUTION AND DETAILED INDEXES ALL for shrlevel change;

Unlike Oracle,DB2 seems to have hundreds of different commands for RUNSTATS and i really dont know which one to use where...!!

 

[blom0344]

Does V8 still work with modifiable query optimize classes?

Try to reset DFT_QUERYOPT from 5 to 7 or 9 to see if the optimiser will take it's time to pick the proper index..

Ties Blom
Information analyst

 

[rbod]

I created Table1 and TEMP_TABLE - both with same columns[just by looking at your queries, for testing purpose].
I created both INDEX1 and INDEX2 on TABLE1.
I tricked the optimizer into believing that I have 7M rows in TABLE1 and 300K rows in TEMP_TABLE. I did the runstats. I then ran the explain plans for the above 2 update queries. I see that query1 is using INDEX1 and query2 is using INDEX2.

 

[Db2curiosity]

Rbod,
That's what we are also trying by doing RUNSTATS (I have given the exact RUNSTATS systax that we are using )

But its behaving wierd..!!

Of course we have lot more columns in Table1 & Temp_Table other than what is used in the query itself...

so,what do you think ? shall i collect RUNSTATS in a different way to better assist the optimizer ?

 

[rbod]

try the following :
1)select * from syscat.indexes where tabname = 'TABLE1'
Compare the FIRSTKEYCARD,FULLKEYCARD and CLUSTERFACTOR for the two indexes.
or
2)reorgchk current statistics on table TABLE1
Compare F4 column[normalized clusterfactor] for both the indexes. The index that has higher clusterratio[clusterfactor*100] is picked.

FYI:
Index access and cluster ratios
When it chooses an access plan, the optimizer estimates the number of I/Os required to fetch required pages from disk to the buffer pool. This estimate includes a prediction of buffer-pool usage, since additional I/Os are not required to read rows in a page that is already in the buffer pool.
For index scans, information from the system catalog tables (SYSCAT.INDEXES) helps the optimizer estimate I/O cost of reading data pages into the buffer pool. It uses information from the following columns in the SYSCAT.INDEXES table:
CLUSTERRATIO information indicates the degree to which the table data is clustered in relation to this index. The higher the number, the better rows are ordered in index key sequence.