MDC vs optimizer

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • martely
    #1

    MDC vs optimizer

    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

    Tags: None
    • Ian
      #2
      Re: MDC vs optimizer

      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?

        Comment

        Previous template Next
        Working...