Re: Deletion Of 160 Million Rows.
- From: "Ryan" <ryan_gaffuri@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Feb 2005 23:23:41 -0500
wolfgang is right. I assumed downtime.
my bad.
----- Original Message -----
From: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, February 08, 2005 10:15 PM
Subject: RE: Deletion Of 160 Million Rows.
> Ordinarily I just ignore your posts, but this advice is rather dangerous.
> Your advices to CTAS nologging the remaining 100 million rows then drop
> and
> rename the tables can loose you data unless you also mention that the
> system needs to be quiesced for the duration - or at least any dml against
> the original table prevented. The OP said that this is an OLTP system.
> CTAS the rows to keep is undoubtedly the fastest, but only safe if you
> have
> that table for yourself for the duration. The delete method will take
> longer, but it won't jeopardize data integrity.
>
> To paraphrase
> Speed is FAR less important than data integrity.
>
> At 02:35 PM 2/8/2005, ryan_gaffuri@xxxxxxxxxxx wrote:
>>I will say this one more time and speaking from experience.... I have
>>worked with large databases in the multi-multi-terabyte range.
>>The other ideas here are wrong. do not follow them.
>>
>>1. The number of records involved is FAR less important than the volume of
>>data. No matter what shop I go to, it takes the longest time to get this
>>concept across to people. Be they DBAs or whatever. When doing bulk
>>processing you are reading the whole table. It is a 'block' algorithm, not
>>a row algorithm.
>>
>>2. The only option for a 1 time removal of this many records is create
>>table as no logging. Delete could take weeks. Anyone who suggests pl/sql
>>does needs to buy a clue. That is never the first option.
>>
>>We had a batch process that ran nightly last year. It would
>>insert,update,delete. We didn't tune it, because we had no way of knowing
>>how many of each we would get. Out of the blue, we got 16 million deletes.
>>It ran for 2 days before someone killed it, then it had to rollback. When
>>then did the create table as nologging.
>>
>>The CTAS requires minimal downtime. you can script. The only time you will
>>be down will be when you drop the old table and do a rename.
>>
>>if you don't have space. Get it. Disk space is cheap. It will cost them
>>more in labor costs to wait for this delete to run. For some reason
>>managers have a hard time understanding that its often cheaper to buy us
>>the hard disk then let us waste time since time is money.
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
