Re: Deletion Of 160 Million Rows.

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Tue, 8 Feb 2005 08:42:17 -0800 (PST)
Message-ID: <20050208164217.41074.qmail@web52608.mail.yahoo.com>

Ryan,

Just a few questions:
> do not do deletes. if this is going to be a regular
> batch process than your new table should be
> implemented with partitioning. Delete is way too
> costly. even with a thin table on most modern
> hardware this will run for days.

Why do you think that deletes will run for days? How are you predicting how long 60M deletes will be?

It is more likely that Oracle Partitioning option is more costly then deletes :) (+ DBA to think about them). Maybe not true when all indexes are local and all your SQL's are written to work with partitioning.

For example, deletes are much easier to do then rebuilding the whole table. Sometimes time is not the most important factor. There are many other factors.

Do not judge before deliberation.

We should first aks Sheldon about all relevant info: a) does it need to be online
b) is the person doing it aware of doing table restructuring

c) what is the timeframe to do this
d) what is the average size of record
e) how many indexes are on the table
f) .....

Regards,
Zoran

ryan_gaffuri_at_comcast.net wrote:

> how long this will take to run is far more
> proportionally to the number of bytes being deleted
> than to the number of records. Since the row is
> copied to the undo tablespace, so you are counting
> bytes.
>
> even if you have a thing 2 column tables with
> numbers. You are looking at days minimum to do this.
>
>
> if this is a one time thing do the following:
>
> 1. create table as nologging with the records you
> want to keep.
> 2. Create all indexes at the same time on the new
> table in nologging mode.
> 3. add constraints
> 4. drop the old table
> 5. rename new table to old table
> 6. rename constraints back to old names.
>
> do not do deletes. if this is going to be a regular
> batch process than your new table should be
> implemented with partitioning. Delete is way too
> costly. even with a thin table on most modern
> hardware this will run for days.

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 08 2005 - 11:45:00 CST