Oracle, deleting many records with child tables

Question

Situation:

• table TBL has ~10k entries for deletion
• table TBL has 14 child tables with delete rule "no action"
• I want to delete 10k entries and referenced entries in child tables

Procedure:

• delete records in child tables
• disable constraints (if constraints are not disabled deletion in next step takes forever)
• delete records in TBL table
• enable constraints

Is there more elegant way for doing this?

[edit] Problem is that third step takes too long because it is checking 14 big tables for existence of non existent records. My procedure has good execution time but I think that there is more elegant way.

Answer 1

Couldn't you change the constraint so that on a delete in the parent table, records in the child table are also deleted?

Answer 2

Can you create a trigger?

Answer 3

It sounds like you need to index your foreign keys on your child tables. Every time you delete a parent record without an index on the child table it must do a full table scan of the child to check if the foreign key constraint was broken.

With the index, it is at worst case an index range scan.

Edit: More info, and a script to determine if this is your problem can be found here. http://asktom.oracle.com/tkyte/unindex/index.html