DB2 Chooses wrong index

Brett Kaiser · #1 (**permalink**) 11-22-04, 12:33

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;

J Petruk · #2 (**permalink**) 11-22-04, 13:43

I take it from your subject it's picking the second instead of the first?

Brett Kaiser · #3 (**permalink**) 11-22-04, 14:16

That would be the case. Any ideas?

Makes no sense....

When I drop Index 2, it picks the correct one....

J Petruk · #4 (**permalink**) 11-22-04, 14:25

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?

Brett Kaiser · #5 (**permalink**) 11-22-04, 14:29

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....

Pat Phelan · #6 (**permalink**) 11-22-04, 14:47

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

Marcus_A · #7 (**permalink**) 11-22-04, 16:00

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.

Brett Kaiser · #8 (**permalink**) 11-22-04, 16:11

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

Marcus_A · #9 (**permalink**) 11-22-04, 18:39

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.

J Petruk · #10 (**permalink**) 11-22-04, 18:54

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.

J Petruk · #11 (**permalink**) 11-22-04, 19:03

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?

Marcus_A · #12 (**permalink**) 11-22-04, 20:17

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.

J Petruk · #13 (**permalink**) 11-22-04, 21:50

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.

xamar · #14 (**permalink**) 11-23-04, 03:46

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?

Marcus_A · #15 (**permalink**) 11-23-04, 05:52

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech