RE: Deletion Of 160 Million Rows.
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 08 Feb 2005 20:15:27 -0700
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
Other related posts:
