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