Sunday, January 24, 2010

Index Usage For Foreign Key Enforcement by DB2

In this post I would like to check how much flexibility DB2 provides regarding using an existing index for foreign key enforcement.


A foreign key, is a type of constraint which tells DB2 to enforce that every value in a "child / details" table exists in a "parent / master" table. As a result, when making updates to the parent table, DB2 must perform some tests against the child table so no rows in the child table will be left without a matching column in the parent table. In most cases, the child table will be significantly larger than the parent table, and since these test are done as a part of the update statement, this statement might take a very long time. A good practice is to create an index on all foreign keys in order to make these tests run faster.


This post is dedicated to these indexes. How flexible can we be when creating these indexes? Lets find out.



In order to perform the test I have created two tables with a foreign key. Then I had a REXX script running a delete statement against the parent table which will force DB2 to check the child table. After that delete statement, the REXX pauses, allowing me to use OMEGAMON to check if DB2 used the index I created.




I created two table with 4 int columns each. The foreign key was created on (col1, col2, col3). Next I will list the indexes I created, and for each one, if DB2 used it or not. At any given moment, only a single index was defined. I mad sure that DB2 will benefit from using the index (by making the child table large)


The following index was used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD               
  (COL1, COL2, COL3)                                              
  USING STOGROUP SYSDEFLT                                         
  PRIQTY 720                                                      
  SECQTY 720                                                      
  DEFER YES                                                       
  BUFFERPOOL BP2;                                                   

The following index was used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD       
  (COL1, COL2, COL3, COL4)                                
  USING STOGROUP SYSDEFLT                                 
  PRIQTY 720                                              
  SECQTY 720                                              
  DEFER YES                                               
  BUFFERPOOL BP2;                                           

The following index was not used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD    
   (COL1, COL4, COL2, COL3)                             
   USING STOGROUP SYSDEFLT                              
   PRIQTY 720                                           
   SECQTY 720                                           
   DEFER YES                                            
   BUFFERPOOL BP2;                                                                                                    

The following index was not used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD   
  (COL3, COL2, COL1)                                  
  USING STOGROUP SYSDEFLT                             
  PRIQTY 720                                          
  SECQTY 720                                          
  DEFER YES                                           
  BUFFERPOOL BP2;                                       

The following index was not used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD   
  (COL1, COL3, COL2)                                  
  USING STOGROUP SYSDEFLT                             
  PRIQTY 720                                          
  SECQTY 720                                          
  DEFER YES                                           
  BUFFERPOOL BP2;                                       

The following index was not used

CREATE UNIQUE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD 

  (COL1, COL4)                                            
  USING STOGROUP SYSDEFLT                                 
  PRIQTY 720                                              
  SECQTY 720                                              
  DEFER YES                                               
  BUFFERPOOL BP2;                                           

The following index was used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD         
  (COL1, COL2, COL3)                                        
  USING STOGROUP SYSDEFLT                                   
  PRIQTY 720                                                
  SECQTY 720                                                
  DEFER YES                                                 
  BUFFERPOOL BP2;                                             

The following index was not used

CREATE UNIQUE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD      
  (COL1, COL3, COL2)                                            
  USING STOGROUP SYSDEFLT                                       
  PRIQTY 720                                                    
  SECQTY 720                                                    
  DEFER YES                                                     
  BUFFERPOOL BP2;                                         



Conclusions:

DB2 only uses an index that is created exactly like the foreign key, or has a prefix that matches the foreign key column sequence, so think twice when designing compound foreign keys. I personally do not understand why the last index I tried was not used. Since it is a Unique index, the optimizer can tell for sure that only a single row will be deleted, so what more can one ask for?




Just another little thingy before I finish. When I thought about checking this issue, I thought maybe I can just check sysibm.syspackdep in order to check if my index is being used. Since I had to code a delete / update statement in order to force DB2 to check the foreign key, and since every delete statement (or update to the foreign key) causes all the indexes to be updated, all the indexes were listed as referenced by that package and this method was not good enough.


Bye for now


Momi

No comments:

Post a Comment