Re: sql question for purge routine

In article <mickmEAysw9.J2w@netcom.com>, mickm@netcom.com (Mickey Mestel) wrote: >hi, > > we are doing a purge here on some huge tables, and this is the way >things are set up. we create a series of temp tables, and the final one has >two columns which are the values we are matching in the table we want to >delete from. this is the query that we are using: > <snip... else I can't post> Hi Mickey, 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? Comments after trying it out at your site (if you do)? HTH. ---------------------- Rudy Fernandes GIC, Kuwait OL 7.20, 4Gl 6.04 ----------------------