Schema considerations for performance with DELETE queries
imaione 2700007WSP Visits (7339)
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" (
ALTER TABLE "IMAIONE "."MAIN_TABLE"
CREATE INDEX IMAIONE.IDX01 ON IMAIONE.MAIN_CHILD
ALTER TABLE "IMAIONE "."MAIN_CHILD"
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:
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.
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.