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 !