DB2 7.2 (7.1.0.68) on AIX 5.2.
I have a query that does a table scan on one database, and an index
scan on another.
The statistics on both databases are up-to-date. Indexes are
identical. Only rowcounts for one of the tables vary (it's using the
index where the table has 1,000 rows, but not where it has 10,000
rows).
Can anyone shed any light on why the optimiser's not picking up the
index consistently?
Here's the offending SQL if helpful:
SELECT MSG.MSG_Q_ID
FROM GFX.VU_INTRL_MSG_Q MSG,
GFX.VU_INTRL_MSG M
WHERE (
( MSG.MEMB_ID = 500000 AND
MSG.REGN_ID IS NULL ) OR
MSG.REGN_ID IN ( SELECT USR.REGN_ID
FROM GFX.VR_USR_ROUTE USR
WHERE USR.REGN_ID = MSG.REGN_ID
AND USR.MEMB_ID = 500000
AND USR.USR_ID =
18006554984185 ) )
AND M.MSG_ID = MSG.MSG_ID
AND ( 'B' IS NULL OR
LOCATE( COALESCE( MSG.MSG_TYPE, M.MSG_TYPE ),
'B' ) > 0 )
AND ( 'DAN' IS NULL OR
LOCATE( M.MSG_CATGY, 'DAN' ) > 0 )
AND ( ( 'A' = 'A' )
OR ( 'A' = 'U'
AND MSG.OWN_USR_ID IS NULL )
OR ( 'A' = 'Y'
AND MSG.OWN_USR_ID IN (SELECT USR_ID
FROM GFX.VU_USR
WHERE
rtrim(UPPR_FULL_NAME) like (rtrim (translate (UCASE ('DAN'), '%',
'*'))) ) )
OR ( 'A' = 'N'
AND MSG.OWN_USR_ID IN (SELECT USR_ID
FROM GFX.VU_USR
WHERE
UPPR_FULL_NAME = ( UCASE('DAN') ) ) )
OR ( 'A' = 'M'
AND MSG.OWN_USR_ID = 18006554984185 )
)
ORDER BY MSG.CRTD_TSTMP DESC
;
Thank you
Bruce
