Performance issue: Need to force Optimizer use a specific Index

[Venkatesh Subbaramu]

Hi Expert DBAs,
I am facing an issue with a query, can anyone help me through?
I have a Query:
UPDATE VMUS.MSG_MSTR a SET MSG_STA_ID =5
WHERE a.PROC_ID =2 AND
a.file_type_id =1 AND
MSG_STA_ID IN (1, 3) AND
DATA_REQ_DT IS NOT NULL
We have 2 indexes for the table:
Indname Column names
XEM0602 +MSG_STA_ID+FIL E_TYPE_ID+PROC_ ID
XEM0605 +PROC_ID+FILE_T YPE_ID+SOURCE_F ILE_NM_TX

Query is using XEM0605 index, but I would like query to use index
XEM0602 which would be more economical. Please suggest a way to
achieve this.
I tried tweaking the sysstat tables no success. Also tried putting
query as
UPDATE VMUS.MSG_MSTR a SET MSG_STA_ID =5
WHERE a.MSG_STA_ID IN (1, 3) AND
a.PROC_ID =2 AND
a.file_type_id =1 AND
DATA_REQ_DT IS NOT NULL
No success.

If instead of the clause , " MSG_STA_ID IN (1, 3) " , I change to
MSG_STA_ID = 1 in UPDATE query optimizer uses my required index
"XEM0602". But for the IN clause it just disregards the field.Please
help.
An early response is well appreciated as issue is holding jobs.

Thanks for your time. ....
Regards
V
------------------------------------------------------------------------
IT Interview Questions : http://www.geekinterview.com IT Tutorials and Articles : http://www.geekarticles.com Oracle and Oracle Apps Training : http://www.exforsys.com

[Serge Rielau]

Venkatesh Subbaramu wrote:

Hi Expert DBAs,
I am facing an issue with a query, can anyone help me through?
I have a Query:
UPDATE VMUS.MSG_MSTR a SET MSG_STA_ID =5
WHERE a.PROC_ID =2 AND
a.file_type_id =1 AND
MSG_STA_ID IN (1, 3) AND
DATA_REQ_DT IS NOT NULL
We have 2 indexes for the table:
Indname Column names
XEM0602 +MSG_STA_ID+FIL E_TYPE_ID+PROC_ ID
XEM0605 +PROC_ID+FILE_T YPE_ID+SOURCE_F ILE_NM_TX

Query is using XEM0605 index, but I would like query to use index
XEM0602 which would be more economical. Please suggest a way to
achieve this.

Hmm, I certainly understand why the optimizer can't resist an index
where it has the two leading key colmuns nailed.

Try this... just a hunch...
UPDATE VMUS.MSG_MSTR a SET MSG_STA_ID =5
WHERE (a.PROC_ID, a.file_type_id, a.MSG_STA_ID)
IN (SELECT * FROM (VALUES(2, 1, 1), (2, 1, 3)) AS X)
AND DATA_REQ_DT IS NOT NULL

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[m0002a]

If you reorg the table based on the index you specified (or make it the
clustering index), and then run runstats with distirubuiton on key
columns, then DB2 will choose the most efficient access path 99.9% of
the time.

[Shashi Mannepalli]

Try this

Make the table VOLATILE.

cheers....
Shashi Mannepalli

m0****@yahoo.co m wrote:

If you reorg the table based on the index you specified (or make it the
clustering index), and then run runstats with distirubuiton on key
columns, then DB2 will choose the most efficient access path 99.9% of
the time.

[m0002a]

Making a table volatile will encourage index usage, but I am not sure
it will encourage one index over another.

[Knut Stolze]

m0****@yahoo.co m wrote:

If you reorg the table based on the index you specified (or make it the
clustering index), and then run runstats with distirubuiton on key
columns, then DB2 will choose the most efficient access path 99.9% of
the time.

And if you run into the other 0.1%, you might want to contact IBM support to
get the problem fixed. I believe the philosophy for DB2 is that the
optimizer is supposed to do the right thing and not that the application
developer has to do the optimizer's job by giving hints.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany