To elaborate on what I meant in your step 5: I teach best by example:
create table H100_tmp as select * from H100 where entryid in (select c1 from T100) / truncate table H100 / insert into H100 select * from H100_tmp / drop table H100_tmp / You may be able to write this more efficiently using the EXISTS clause instead of the IN clause, but my knowledge is not there to write it in an email without testing it. Axton Grams On 9/19/07, Patel, Kamlesh <[EMAIL PROTECTED]> wrote: > Axton and others, > Thanks for quick replies and suggestions. > > I will work with our dBA to quickly document exact steps and process. I > will provide to the list my updates as available including exact steps I > followed to achieve my goals. > > My understanding: > 1. Copy data records you want to keep into a temp table from original > table > 2. Truncate original table > 3. Insert into (now empty) original table all data records from temp > table > 4. Drop temp table. > 5. Repeat this process (steps 1 to 4) for B#, B#C#, and H# table for > every T# table I touch in step 1 to 4. > > Kamlesh > > > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[EMAIL PROTECTED] On Behalf Of Axton > Sent: Tuesday, September 18, 2007 7:07 PM > To: [email protected] > Subject: Re: delete mass amount of records from ARS 5.1.2 > > If on Oracle, truncating the tables is the best way, as it resets the > high water marks for storage on the table. Imagine that a table scan > on a table with 10 rows could have the same number of physical reads > as the same table with 1m rows if the high water marks are the same. > If you need to retain some of the data, use this as a guide: > > create table T100_tmp as select * from T100 where ... > / > truncate table T100 > / > insert into table T00 select * from T100_tmp > / > drop table T100_tmp > / > > rinse and repeat for the B#, B#C# and H# tables, using the resulting > data in the T# table as the guide for which rows to retain from each. > > This will allow you retain the data you want while resetting the high > water marks. > > Axton Grams > > On 9/18/07, Roger Justice <[EMAIL PROTECTED]> wrote: > > ** Truncate the table is the simplest way. > > > > > > > > -----Original Message----- > > From: Patel, Kamlesh <[EMAIL PROTECTED]> > > To: [email protected] > > Sent: Tue, 18 Sep 2007 3:46 pm > > Subject: delete mass amount of records from ARS 5.1.2 > > > > > > ** > > > > All, > > I am going through an upgrade plan to 7.x. Before I begin my upgrade > I > > would like to remove lots of data records form our remedy forms. I > have a > > pretty good idea about remedy 5.1.2 patch 1375 data structure. I > would like > > to find out if there is alternative safe and fast way to remove up to > > 1,000,000 records from couple of remedy forms. Most other forms are > > relatively small but I would like to employ a fast delete operation to > > minimize the outage period. > > > > Thank You > > > > Kamlesh Patel > > __20060125_______________________This posting was > > submitted with HTML in it___ > > ________________________________ > > Email and AIM finally together. You've gotta check out free AOL Mail! > > > > __20060125_______________________This posting was > > submitted with HTML in it___ > > ________________________________________________________________________ > _______ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where > the Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the > Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
