RE: Deletion Of 160 Million Rows.
- From: ryan_gaffuri@xxxxxxxxxxx
- To: thomas.mercadante@xxxxxxxxxxxxxxxxx, "'kristian@xxxxxxxx'" <kristian@xxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 08 Feb 2005 21:35:31 +0000
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.
> -----Original Message-----
> From: Kristian Myllym=E4ki [mailto:kristian@xxxxxxxx]=20
> Sent: Tuesday, February 08, 2005 11:49 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: Deletion Of 160 Million Rows.
>
>
> I surely would prefer the CTAS option described in the thread, but if
> going with the other option, why use a temp table at all?
>
> Why not instead loop through some batches of deletes until no more rows
> are affected by your delete condition?
>
> begin
> loop
> delete from [table]
> where [your delete condition]
> and rownum < 10000; -- or whatever size you prefer
--
//www.freelists.org/webpage/oracle-l
Other related posts:
