Re: sql question for purge routi

Would I be correct in assuming: 1) You do not have a lot of activity on the database at the time you do the purge. 2) You have very large logs to deal with the transaction (assuming the table is large). 3) The table itself is not being used at the time of the purge. Mark MarkDenham@aol.com (home) Denham.M@amstr.com (work) --------------------------( Forwarded letter 1 follows )--------------------- Date: Sunday, 1 June 1997 6:33pm From: Nils.Myklebust@idg.no (Nils Myklebust) Subject: Re: sql question for purge routine Date: Sun, 01 Jun 1997 19:47:33 GMT Reply-To: Nils.Myklebust@idg.no Organization: NM Data AS Sender: informix-list-owner@rmy.emory.edu To: informix-list@rmy.emory.edu X-Informix-List-Id: <news.38610> 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