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

result is something like :

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 !

€1,00

Advertisements

Leave a Reply

Your email address will not be published.

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