Chobicus
Chobicus

Reputation: 2094

Deleting many records with child tables

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

Answers (1)

Daniel Emge
Daniel Emge

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

Related Questions