MDC vs optimizer

[martely]

Hello,
DB2 V8 FP10

We are doing some performance tunning with an MDC table and we found
unexpected access plans with columns used for range and IN predicates
(Optimization class 7).

Our MDC have 2 dimensions, ColDim1 and ColDim2

Query1:

Select Col1,Col2, Col3

>From MDC_Table

Where
ColDim1 = 'YYYYMM'
and Col_Dim2 IN (1,2,3,4)

In Query1 DB2 uses DimensionIndex on ColDim1 but if we replace the IN
predicate with ORs
(ColDim1 =1 OR ColDim1=2 OR ColDim1=3 OR ColDim1=4) then the
optimizer choose the composite BLOCK Index on ColDim1 and ColDim2.

Query2

Select Col1,Col2, Col3

>From MDC_Table

Where
ColDim1 = 'YYYYMM'
and Col_DIM1 BETWEEN 3 and 5

In Query2 DB2 uses DimensionIndex on ColDim1 instead of composite
BLOCK Index on ColDim1 and ColDim2.

Anyone have an explanation ?

Thanks

[Ian]

martely wrote:

Hello,
DB2 V8 FP10

We are doing some performance tunning with an MDC table and we found
unexpected access plans with columns used for range and IN predicates
(Optimization class 7).

Our MDC have 2 dimensions, ColDim1 and ColDim2

Query1:

Select Col1,Col2, Col3

>>From MDC_Table

Where
ColDim1 = 'YYYYMM'
and Col_Dim2 IN (1,2,3,4)

In Query1 DB2 uses DimensionIndex on ColDim1 but if we replace the IN
predicate with ORs
(ColDim1 =1 OR ColDim1=2 OR ColDim1=3 OR ColDim1=4) then the
optimizer choose the composite BLOCK Index on ColDim1 and ColDim2.

Query2

Select Col1,Col2, Col3

>>From MDC_Table

Where
ColDim1 = 'YYYYMM'
and Col_DIM1 BETWEEN 3 and 5

In Query2 DB2 uses DimensionIndex on ColDim1 instead of composite
BLOCK Index on ColDim1 and ColDim2.

Anyone have an explanation ?

Not without seeing the explain plans, because many things could cause
this. What is the column order in the composite block index?