Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: DB2 Chooses wrong index

    I have this DML

    Code:
    UPDATE BXBB72DA.PRU_REL                           
       SET BU_CD              = :BU_CD                  
    --the enitre row
              WHERE PERSON_ID     = :PERSON-ID              
                AND ID_TYPE_CD    = :ID-TYPE-CD                     
                AND ASSOC_TYPE_CD = :ASSOC-TYPE-CD                  
                AND CONTRACT_NUM  = :CONTRACT-NUM                       
                AND EFFECTIVE_DT  = :EFFECTIVE-DT
    Which indexe do you think DB2 would use?

    Code:
    CREATE TYPE 2 UNIQUE INDEX BXBB72DA.GUXPPREL
           ON BXBB72DA.PRU_REL                  
           (PERSON_ID            ASC            
           ,ID_TYPE_CD           ASC            
           ,ASSOC_TYPE_CD        ASC            
           ,CONTRACT_NUM         ASC            
           ,EFFECTIVE_DT         ASC            
           ,SOURCE_SYSTEM_ID     ASC  )         
           USING STOGROUP BXBB7201              
                 PRIQTY 8260                    
                 SECQTY 1380                    
                 ERASE NO                       
           FREEPAGE 0                           
           PCTFREE 10                    
           BUFFERPOOL BP0                
           CLOSE YES                     
           PIECESIZE 2097152  K;         
                                         
    CREATE TYPE 2 INDEX BXBB72DA.GUX2PREL
           ON BXBB72DA.PRU_REL           
           (CONTRACT_NUM         ASC  )  
           USING STOGROUP BXBB7201       
                 PRIQTY 8260             
                 SECQTY 1380             
                 ERASE NO                
           FREEPAGE 0                    
           PCTFREE 10            
           BUFFERPOOL BP0        
           CLOSE NO              
           PIECESIZE 2097152  K;
    Last edited by Brett Kaiser; 11-23-04 at 10:26.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I take it from your subject it's picking the second instead of the first?
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That would be the case. Any ideas?

    Makes no sense....

    When I drop Index 2, it picks the correct one....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Brett Kaiser
    That would be the case. Any ideas?

    Makes no sense....

    When I drop Index 2, it picks the correct one....
    That's strange behaviour. I assume your stats are current? Have you tried a different query optimization level? Shouldn't make a difference.

    What platform are you on? From your create index statement I assume it isn't Linux/UNIX/Windows DB2?
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Big Iron

    Yup stats are run...The first index even has a clustering ratio around 95%

    AND the other index has at least 50% or more made up of spaces....

    I suggested we lose that index, but they want an explanation...

    I have no clue with this...been working with DB2 for a VERY long time, and I've never seen this before....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd bet on selectivity. The Z-Series optimizer is very big on selectivity as opposed to coverage (which makes sense in that environment).

    -PatP

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not 100% sure about this, but I believe (after glancing at the DB2 catalog tables) that DB2 for z/OS only keeps stats for FIRSTKEYCARDF and FULLKEYCARDF. So if you only supply the first 5 columns of a 6 column index in the predicate, DB2 does not know the exact filter factor. Not sure about DB2 V8.1 for z/OS.

    DB2 V8.1 for LUW keeps stats on FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, and FULLKEYCARD, so it has a better idea which index to use.

    Also, If the contract_num has a reasonably high filter factor, and the index is much smaller than the first index, that is another reason it might be favored. Check the cardinality of the second index, it might higher than you think.

    Note: the 2 indexes will not use the same amount of space, so your space allocations are off.
    Last edited by Marcus_A; 11-22-04 at 17:03.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EDIT: Version 7.2 btw

    I get

    Code:
    INDEXSPACE  FIRSTKEYCARD  FULLKEYCARD        NLEAF  NLEVELS 
    ----------  ------------  -----------  -----------  ------- 
    GUXPPREL          442477       538783         6735        3 
    GUX2PREL          311094       311094         1600        3
    EDIT:

    And

    Code:
    CLUSTERRATIO
    ------------
              95
              52
    
      PIECESIZE  COPY  COPYLRSN  CLUSTERRATIOF      SPACEF 
    -----------  ----  --------  -------------  ---------- 
        2097152  N                9.521069E-01   2.736E+04 
        2097152  N                5.207011E-01   8.640E+03
    Last edited by Brett Kaiser; 11-22-04 at 17:15.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    So the cardinality of the predicate for using index 2 is 311,094. DB2 does not know what the cardinality of the first 4 columns (out of 6) for index 1, but just on a pro-rata basis it is equal to (4/6*538,783 = 359,189). But as far as DB2 is concerned it could be much lower, so DB2 decided to take the less risky approach and use index 2.

    In truth, the cardinality of the first 4 columns of index 1 is always higher than the cardinality of index 2, but DB2 doesn't know that.

    I doubt that you could detect a performance difference of using one index over the other.

    In summary, based on what DB2 knows about the data, it made a good choice.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Marcus_A
    So the cardinality of the predicate for using index 2 is 311,094. DB2 does not know what the cardinality of the first 4 columns (out of 6) for index 1, but just on a pro-rata basis it is equal to (4/6*538,783 = 359,189). But as far as DB2 is concerned it could be much lower, so DB2 decided to take the less risky approach and use index 2.

    In truth, the cardinality of the first 4 columns of index 1 is always higher than the cardinality of index 2, but DB2 doesn't know that.

    I doubt that you could detect a performance difference of using one index over the other.

    In summary, based on what DB2 knows about the data, it made a good choice.
    Excellent

    I'm not sure if z-series supports it, but this could be a good candidate for including columns in this manner:

    CREATE UNIQUE INDEX <BLAH>
    ON BXBB72DA.PRU_REL
    (PERSON_ID ASC
    ,ID_TYPE_CD ASC
    ,ASSOC_TYPE_CD ASC
    ,CONTRACT_NUM ASC)
    INCLUDE(EFFECTIVE_DT, SOURCE_SYSTEM_ID )

    Assuming the combination of those 4 columns is unique...

    Leaves the option open for index-only access (if that's important in your environment) while giving a more accurate picture to the optimizer.
    --
    Jonathan Petruk
    DB2 Database Consultant

  11. #11
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Marcus_A
    So the cardinality of the predicate for using index 2 is 311,094. DB2 does not know what the cardinality of the first 4 columns (out of 6) for index 1, but just on a pro-rata basis it is equal to (4/6*538,783 = 359,189). But as far as DB2 is concerned it could be much lower, so DB2 decided to take the less risky approach and use index 2.

    In truth, the cardinality of the first 4 columns of index 1 is always higher than the cardinality of index 2, but DB2 doesn't know that.

    I doubt that you could detect a performance difference of using one index over the other.

    In summary, based on what DB2 knows about the data, it made a good choice.
    I was thinking more about this.

    If the cardinality of CONTRACT_NUM is 311,094, how could the cardinality of anything that includes it be any lower? It's not possible... so DB2 could be made smarter to make a better choice, by comparing the fields included in each index? Possibly it does this at higher optimization levels?
    --
    Jonathan Petruk
    DB2 Database Consultant

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure if DB2 z/OS has different levels of optimization like DB2 LUW (it did not the last time I used it extensively, but it could have changed since then). So DB2 for z/OS tries not go overboard spending too much time looking at everything (like noticing that index 1 contains the fullkeycard of index 2). I doubt seriously that anyone could measure the difference of DB2 using index 1 versus index 2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Marcus_A
    I am not sure if DB2 z/OS has different levels of optimization like DB2 LUW (it did not the last time I used it extensively, but it could have changed since then). So DB2 for z/OS tries not go overboard spending too much time looking at everything (like noticing that index 1 contains the fullkeycard of index 2). I doubt seriously that anyone could measure the difference of DB2 using index 1 versus index 2.
    Thanks. Interesting thread.
    Hopefully Brett is as satisfied as I am with that answer.
    --
    Jonathan Petruk
    DB2 Database Consultant

  14. #14
    Join Date
    Apr 2004
    Posts
    64

    Re

    But when the FIRSTKEYCARD is 442477, higher than the second index, what is the point in calculating the pro-rata basis from FULLKEYCARD ? It has to be greater than FIRSTKEYCARD. Could you explain Marcus?

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, I cannot explain, other than index 2 is much smaller than index 1. I don't understand why you are so concerned. I don't think you can measure the difference in performance between using index 1 or index 2. If you can, please publish the results here.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •