Home » SQL & PL/SQL » SQL & PL/SQL » How to delete rows from parent table without scanning child tables?
How to delete rows from parent table without scanning child tables? [message #328022] Wed, 18 June 2008 11:20 Go to next message
dksampat
Messages: 12
Registered: December 2006
Junior Member
We need to delete 1 million records from a 4 million record table "A". This table "A" has around 200+ child tables.

What happens now is: For every row to be deleted in parent table "A", it does a scan on all the 200+ child tables to see whether any Child Record is found. I know that all the 200+ child tables dont have any child record for those 1 million parent records (that are meant to be deleted). Hence the child table scan is unnecessary here. We also dont want to disable/enable constraints as it takes a lot of time.

Is there any other way/hint by which we can surpass this Child Table scan during deletes as it is consuming a lot of time?
Re: How to delete rows from parent table without scanning child tables? [message #328028 is a reply to message #328022] Wed, 18 June 2008 11:31 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Index your foreign keys.

Regards
Michel
Previous Topic: SQL - select/delete data date older than today(3 days)
Next Topic: Question on how Merge works with a comibination of Update and Delete
Goto Forum:
  


Current Time: Mon Dec 05 11:17:34 CST 2016

Total time taken to generate the page: 0.05360 seconds