Reputation: 2094
Situation:
Procedure:
Is there more elegant way for doing this?
The 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.
Upvotes: 1
Views: 2856
Reputation: 1617
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
Upvotes: 4