Delete from DB2 table with many FK constraints
DB2 11.5 on Windows
I have a large table that I need to purge of data greater than 10 years old. This table is a main table and has Foreign Keys to 7 other large tables. When I do a delete based on the table's Primary Key and look at the explain plan it looks like this.
Deleting 2,000 rows takes about 50 seconds and there are millions of rows in the table that need to be deleted.
I need to do this incrementally so dropping all the constraints and then adding them back in is not really feasible since new data will be coming into the system that will need those keys.
Any suggestions?
Jim
The foreign keys in this direction shouldn't have much impact if the deletes don't cascade.
I do have to wonder though why it takes 50 seconds to delete 2,000 rows. That seems excessive.
Are there indexes on all of the column you're using to control the delete?
ASKER
On the main table it is the PK. The tables with FK's are all indexed. If you look at the explain plan it seems to be taking forever scanning the tables with the FKs even using indexing.
ASKER
None of the FK's are set to cascade and I have removed all (I think) of the child records from the connected tables. I was just hoping there was a trick to be able to delete them more quickly.
Thank you.