Re: sql question for purge routine

In article <3391d14a.31310271@gate.idg.no>, Nils.Myklebust@idg.no (Nils Myklebust) wrote: >rferdy@kuwait.net (Rudy Fernandes) wrote: <snip> >:I've used a different approach in a similar situation to yours which >:works on the following premises. >: >:1. OL7 index creation is super-fast. >:2. Deleting a large number of rows is very slow, especially when >: there are indexes and when OL is being logged. >:3. Deleting a large number of rows from a table could skew its >: b-tree badly. >: >:On this basis, the approach I followed was : >: >:1. Create a target_table with a structure identical to tab_to_purge >: but without indexes. >:2. insert into target_table select required rows from tab_to_purge >:3. rename tab_to_purge to about_to_be_dropped >:4. rename target_table to tab_to_purge >:5. drop about_to_be_dropped >:6. create indexes on tab_to_purge >:7. Update Stats on tab_to_purge >: >:Comments? > >Are you doing like us, no referential integrity spesifications in the >database? Otherwhise how do you handle that? > That's right - we are only recently started experimenting with referential constraints into our databases - our applications are supposed to do the task. But you have put your finger on what appears to be a potentially disastrous situation. What if there are child tables referencing tab_to_purge with an ON DELETE CASCADE constraint? On tab_to_purge being dropped, will all these children be deleted? On investigation I found that the children of rows in a table being dropped do not get deleted. However, referential constraints get dropped. But that still leaves the problem of parentless rows if tab_to_purge has children tables with the ON DELETE CASCADE constraint. In other words, take care. ----------------------- Rudy Fernandes GIC, Kuwait OL 7.20UC4, 4GL 6.04UC1 -----------------------