Re: sql question for purge routine

rferdy@kuwait.net (Rudy Fernandes) wrote: :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? Are you doing like us, no referential integrity spesifications in the database? Otherwhise how do you handle that? :Comments after trying it out at your site (if you do)? Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org