Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Deletion Of 160 Million Rows.

Re: Deletion Of 160 Million Rows.

From: Ryan <>
Date: Tue, 8 Feb 2005 23:23:41 -0500
Message-ID: <01a001c50e5f$236e4b80$350a5444@ryannew>

wolfgang is right. I assumed downtime.

my bad.

> 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, 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
> --

Received on Tue Feb 08 2005 - 23:26:24 CST

Original text of this message