Hi All,
db2 V8.2fp14
OS: AIX

i have a question regarding the below query

SELECT m.value ,
m.unitTypeForValue ,
m.measurementSeq ,
m.classType ,
m.pipelineRunDate ,
m.name
FROM UDB.CS_Measurement m
WHERE m.name = ?
AND m.periodSeq = ?
AND m.positionSeq = ?
AND m.payeeSeq = ?

Access Plan:
-----------
Total Cost: 26.361
Query Degree: 12

Rows
RETURN
( 1)
Cost
I/O
|
1
LTQ
( 2)
26.361
3
|
1
FETCH
( 3)
26.1561
3
/---+---\
12.9989 3.61639e+07
IXSCAN TABLE: UDB
( 4) CS_MEASUREMENT
17.4407
2
|
3.61639e+07
INDEX: UDB
CS_MEASURE_IX1


Total no of records in the table : 36 million.

INDEX_NAME: INDEX_COLUMNS: UNIQUERULE:
----------- --------------------------------------------------- -----------
CS_MEASUREMENT_PER +POSITIONSEQ+PERIODSEQ D
CS_MEASURE_IX1 +PERIODSEQ+POSITIONSEQ D
CS_MEASUREMENT_PK +MEASUREMENTSEQ+PIPELINERUNSEQ P
CS_MEASUREMENT_AK1 +NAME+PAYEESEQ+POSITIONSEQ+PERIODSEQ+PIPELINERUNSE Q U


CLONAME: COLCARD
-------------- -------
NAME 18
PAYEESEQ 20992
POSITIONSEQ 21221
PERIODSEQ 147
PIPELINERUNSEQ 1120

this query is taking long time even though it is using index scan.

My question is why is this query using the index CS_MEASURE_IX1 istead of CS_MEASUREMENT_AK1 as CS_MEASUREMENT_AK1 has total
5 columns out of which 4 columns are in the whereclause of the query.

is it because of the first column of the CS_MEASUREMENT_AK1 index has low cardinality this index is not beeing used?
any suggestion on improving the performance of this query?

I think that intra-partition parallelism may play a role in it. What happens if you set the degree of parallelism to 1 before running the query? Do you really have 12 processors to play with?

Also, can you post the output of "REORGCHK CURRENT STATISTICS ON TABLE UDB.CS_Measurement"?

This is indeed unexpected, at least from the information you provided.
Could you try replacing "AND m.periodSeq = ?" by "AND m.periodSeq = ? + 0"
(that is, assuming periodSeq is numeric; otherwise add "|| ''").
This should discourage the use of CS_MEASURE_IX1, hence possibly turn into using CS_MEASUREMENT_AK1.

seting the query degree to 1 also did not forced to use the CS_MEASUREMENT_AK1 index. even if i include all the columns in this index in the where clause the index is not changed.
this table does not need reorg.

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: UDB.CS_MEASUREMENT
UDB CS_MEASUREMENT 3.6e+07 0 1e+06 1e+06 - 1.86e+10 0 97 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: UDB.CS_MEASUREMENT
UDB CS_MEASURE_IX1 4e+07 17146 0 3 16 0 3e+06 38 84 3 0 0 *----
UDB CS_MEASUREMENT_AK1 4e+07 2e+05 0 4 54 0 4e+07 0 88 31 0 0 *----
UDB CS_MEASUREMENT_PER 4e+07 17146 0 3 16 0 3e+06 0 84 3 0 0 *----
UDB CS_MEASUREMENT_PK 4e+07 61714 0 3 16 0 4e+07 100 89 0 0 0 -----
-------------------------------------------------------------------------------------------------


evven adding 0 to Periodseq did not helped its the same access plan.

any other suggestions please?

Don't know if you can afford a reorg on that table, but I think that reorganizing it using CS_MEASUREMENT_AK1 might help.