Using temporary indices to optimize DELETE operations on big tables in #DB2

By | November 21, 2017

If the DELETE operations are much faster in case we disable constraints then this is because not all foreign keys affected by the DELETE are indexed.

We have to detect all the foreign keys that point to the table from where you delete date. The you can create temporary indices to help you speed up the delete.

STEP 1. Detect all he foreign keys that point to the table on which DELETE will be executed TRANSACTION
Execute the following query

select substr(tabname,1,30) table_name,substr(constname,1,20)  fk_name,substr(REFTABNAME,1,30) parent_table,substr(refkeyname,1,30) pk_orig_table,fk_colnames from syscat.references where tabname = 'TRANSACTION';

result is something like :

TRANSACTION                    FK76A35E5C2E6581E7   PARTYIDENTIFICATION        SQL121207182559530        CREDITOR_ID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
TRANSACTION                    FK76A35E5C3291208D   PARTYIDENTIFICATION        SQL121207182559530        DEBTOR_ID

STEP 2. Create temporary indices
Create an index on column CREDITOR_ID on PARTYIDENTIFICATION and an index on column DEBTOR_ID on PARTYIDENTIFICATION

STEP 3. Execute the DELETE
Now we can perform a DELETE on TRANSACTION that will affect a big number of entries.

STEP 4. Delete the temporary indices
Delete all the indices created at STEP 2. We do not need unnecessary indices on the production environment.

Contribute to this site maintenance !

This is a self hosted site, on own hardware and Internet connection. The old, down to earth way 🙂. If you think that you found something useful here please contribute. Choose the form below (default 1 EUR) or donate using Bitcoin (default 0.0001 BTC) using the QR code. Thank you !



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.