IBM Support

Schema considerations for performance with DELETE queries

Technical Blog Post


Abstract

Schema considerations for performance with DELETE queries

Body

When using DELETE queries,  especially searched deletes which may delete many rows at a time,  there are index considerations which can be important to getting good performance,  if there are foreign key constraints present.  These are helpful to be aware of in advance,  since the absence of the right indexes may result in suboptimal delete performance,  and the cause may not be immediately evident from looking at the plan graph in a explain (db2exfmt output).  

  A typical scenario of this kind might look like the following:  

DELETE FROM IMAIONE.MAIN_TABLE WHERE C_ID1 = ?

MAIN_TABLE has a primary key,  and is referenced by a foreign key constraint on MAIN_CHILD

CREATE TABLE "IMAIONE  "."MAIN_TABLE"  (
          "C_ID1" INTEGER NOT NULL ,
          "C_ID2" INTEGER NOT NULL ,
          "C_ID3" INTEGER NOT NULL
                :
                :
         IN "USERSPACE1"
         ORGANIZE BY ROW;

ALTER TABLE "IMAIONE  "."MAIN_TABLE"
    ADD CONSTRAINT "PKEY" PRIMARY KEY
        ("C_ID1",
         "C_ID2",
         "C_ID3");


CREATE TABLE "IMAIONE  "."MAIN_CHILD"  (
          "D_ID1" INTEGER NOT NULL ,
          "D_ID2" INTEGER NOT NULL ,
          "D_ID3" INTEGER NOT NULL,
          "DCOL1" INTEGER,  
                :
                :
         IN "USERSPACE1"
         ORGANIZE BY ROW;

CREATE INDEX IMAIONE.IDX01 ON IMAIONE.MAIN_CHILD
                ("DCOL1" ASC,
                 "D_ID1" ASC,
                 "D_ID2" ASC ,
                 "D_ID3" ASC )
                COMPRESS NO
                INCLUDE NULL KEYS ALLOW REVERSE SCANS;

ALTER TABLE "IMAIONE  "."MAIN_CHILD"
    ADD CONSTRAINT "FKPK_MAIN" FOREIGN KEY
        ("D_ID1",
         "D_ID2",  
         "D_ID3")
    REFERENCES "IMAIONE  "."MAIN_TABLE"
        ("C_ID1",
         "C_ID2",
         "C_ID3")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

  Defining a PK-FK relationship requires specifying a constraint on the parent table (in this case MAIN_TABLE),  and this automatically creates a matching unique index which can be used to efficiently lookup rows based on the key.  However, in general defining the foreign key constraint on the child table does not require that there is a such a matching index on the child table.  In this case there is the PK index on (C_ID1, C_ID2, C_ID3),  and there is an index IDX01 on MAIN_CHILD,  but this index does not quite match the FK definition.  For a searched DELETE of the kind above,  this can negatively impact performance,  because the access plan may need to lookup rows in MAIN_CHILD as part of the DELETE processing.  

  In this specific example,  the foreign key is defined with ON DELETE NO ACTION,  but is marked as ENFORCED.  Despite the fact that there is no action defined as necessary on a delete from the parent table,  the fact that the constraint is ENFORCED will still require MAIN_CHILD to be accessed in the query plan.  For qualifying rows on MAIN_TABLE which are identified for deletion,  ENFORCED implies that if there are still any rows in MAIN_CHILD which contain the PK value for the candidate row,  an error reporting SQL0532N must be triggered.  This check is constructed as part of the query logic,  in this case in the Optimized SQL in the explain output,  there may be a clause like the following:  

             WHEN EXISTS
                  (SELECT
                     $RID$
                   FROM
                     IMAIONE.MAIN_CHILD AS Q5
                   WHERE
                     (Q4.C_ID1 = Q5.D_ID1) AND
                     (Q4.C_ID2 = Q5.D_ID2) AND
                     (Q4.C_ID3 = Q5.D_ID3)
                  )
             THEN RAISE_ERROR(-532, 'IMAIONE.FKPK_MAIN.')

  In the access plan graph,  this is likely to result in a plan which (a) has an access to the target table of the delete,  to find the rows which match the search condition and feed those rows to a DELETE operator,  and (b) and an additional access which is present for the error checking.  

                    +-----------------------+---------
                   7500                 4.71387e-09
                  DELETE                  IXSCAN
                  (   3)                  (   6)
                  20.4109                 48.9874
                  3.01187                   15
               /----+----\                  |
            7500         100000            2000
           FETCH     TABLE: IMAIONE    INDEX: IMAIONE
           (   4)       MAIN_TABLE        IDX01
           20.3306         Q1               Q5
              3
         /---+----\
      7500        100000
     IXSCAN   TABLE: IMAIONE
     (   5)      MAIN_TABLE
     13.556         Q2
        2
       |
     10000
 INDEX:
       Q2

   Because this additional access must be repeated for every row flagged for deletion,  the efficiency of this scan can be important.  From the graph, this seems to be efficient because it shows an IXSCAN with index-only access.  However,  because of the lack of matching index definition to the FK columns,  this index scan will be scanning all the rows of the table for every deleted row.   This isn't evident without checking the operator details in the db2exfmt output,  it will show that all the predicates are applied as sargable predicates,  not start-stop keys (and thus are not range delimiting).  

  In this case,  although the D_ID1, D_ID2, and D_ID3 columns are present in the index,  the presence of the non-FK column DCOL1 as the leading column of the index prevents range delimiting the scan.  This can be negative both because it's more costly to scan the whole index than to do a single lookup,  but because it may lead to more locking during the DELETE processing which has greater potential for lock contention with other queries.  

        Predicates:
        ----------
        3) Sargable Predicate,
            Comparison Operator:        Equal (=)
            Subquery Input Required:    No                                                           
            Filter Factor:          0.0005                                                           
                                                                                                     
            Predicate Text:                                                                          
            --------------                                                                           
            (Q4.C_ID1 = Q5.D_ID1)                                                                


        4) Sargable Predicate,                                                                       
            Comparison Operator:        Equal (=)                                                    
            Subquery Input Required:    No                                                           
            Filter Factor:          2.50401e-05                                                      
                                                                                                     
            Predicate Text:                                                                          
            --------------                                                                           
            (Q4.C_ID2 = Q5.D_ID2)                                                               
                                                                                                     
                                                                                                     
        5) Sargable Predicate,                                                                       
            Comparison Operator:        Equal (=)                                                    
            Subquery Input Required:    No                                                           
            Filter Factor:          0.000188253                                                      
                                                                                                     
            Predicate Text:                                                                          
            --------------                                                                           
            (Q4.C_ID3 = Q5.D_ID3)     

     

   To avoid this kind of situation,  ensuring that there is an index which matches the FK columns (they can include other columns as long as all of the FK columns are the leading columns on the index) is effective. 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140436