Last post Nov 22, 2012 11:15 AM by Basquiat
Nov 21, 2012 12:39 PM|mspblr2000|LINK
I need to delete large number of records (in terms of crores) with dependent records from different table (total 44 dependent tables which are one - one and one - many relation).
I have query which returns the number for records to delete. Now i am using cursor to delete dependent table records. But it takes more time to delete records. It took 1 hr 30 min to delete 8000 records (each record from 8000 has dependent records from 44
So, please suggest me the best way to delete the large number of records.
Nov 21, 2012 01:05 PM|Basquiat|LINK
Cant you use ON DELETE CASCADE ?
If the data deleted in you table is referenced in other tables using foreign keys...the data in the other tables will also be deleted
Nov 22, 2012 08:13 AM|mspblr2000|LINK
Thanks for reply,
I have tested using ON DELETE CASCADE. it's working better then what i am using (cursor). But still i need to make it fast. For the production database the entair process i want to finish it in 4 to 5 hours (to delete around 1 crore or more records). But
cursor and On DELETE CASCADE will take in terms of days to finish.
Let me know if you come to know any better solutions
Nov 22, 2012 08:47 AM|Basquiat|LINK
I don't have other suggestions.
But I can't imagine it taking that long....except maybe if there is really a lot of related records
Nov 22, 2012 11:08 AM|mspblr2000|LINK
I have around 40 lakhs records in master tables. 43 other tables are dependent to master table and those are one - one and one - many relations.
Nov 22, 2012 11:15 AM|Basquiat|LINK
I don't think there is a method that will be faster than the CASCADE