Table scans for larger rowcounts?

[Bruce Pullen]

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_MS G_Q MSG,
GFX.VU_INTRL_MS G 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_ROUT E 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

[AK]

are the cluster factors identical?

[Bruce Pullen]

No, but those indexes with lower cluster ratios are being scanned?!?

INDNAME CLUSTERFACTOR
------------------ ------------------------
XAK1INTRL_MSG_Q +1.000000000000 00E+000*
XIE1INTRL_MSG_Q +9.991596638655 46E-001*
XIF11168AINTRLM SGQ +1.000000000000 00E+000
XIF11168BINTRLM SGQ +7.773109243697 48E-001
XIF1196INTRL_MS G_Q +8.840336134453 78E-001
XIF1265INTRL_MS G_Q +7.773109243697 48E-001
XPKINTRL_MSG_Q +1.000000000000 00E+000

INDNAME CLUSTERFACTOR
------------------ ------------------------
XAK1INTRL_MSG_Q +9.807841614906 83E-001
XIE1INTRL_MSG_Q +9.993206521739 13E-001
XIF11168AINTRLM SGQ +9.807841614906 83E-001
XIF11168BINTRLM SGQ +6.248059006211 18E-001
XIF1196INTRL_MS G_Q +7.697010869565 22E-001
XIF1265INTRL_MS G_Q +6.340256211180 12E-001
XPKINTRL_MSG_Q +9.807841614906 83E-001

*Those indexes being scanned.

[AK]

well, the cluster factors are almost identical.
what about the percentage of rows that satisfy the criteria?

For instance, if the query uses an index and returns 50 rows out of 1K
(0.5%), using an index is justified.
If the same query scans the 10K table and returns 8500 rows out of 10K
(85%), that optimizer's choice to scan the table also makes perfect
sense

[Bruce Pullen]

The query I'm using returns 0 record(s) selected on both databases.
I'm thinking maybe this has something to do with IO / tablespace
container configuration, just not sure how to confirm this. Any tips
much appreciated.
Thanks

[AK]

you might also want to use OPTIMIZE FOR FIRST and / or SELECTIVITY
clause
that might influence the optimizer to choose the index.
Then you could compare real execution costs and see if choosing the
index what the right thing to do