RE: How to project the time a delete will take

From: <nelson.petersen_at_homehardware.ca>
Date: Fri, 30 Jan 2004 09:25:24 -0500
Message-Id: <04Jan30.093203est.119162@stj101.homehardware.ca>

Hi John (Yannis?),

Assumptions:

You are deleting from one table.
There are no referential constraint issues. (You have disabled or dropped them.)
You are deleting 80% or more of the data from this table. (Anything less makes my suggestion less appropriate.)

I don't have a way for you to estimate how long the delete will take. I have a suggestion, though.

What version of Oracle are you running?

If you're on Oracle 8.1.5 or higher (I think EXPORT has the QUERY option as of 8.1.5)
you might consider the following:

EXPORT ... QUERY="WHERE ..." file=data_to_be_left.dmp etc.
>>> The WHERE clause should select the data that you do *NOT* want to
delete.

Truncate the table.
IMPORT file=data_to_be_left.dmp ... etc.

This is a quick way to delete large amounts of data. REDO generated by IMPORT might be less than that generated by DELETE.

As always, test on a test database before trusting this in production! If you are on earlier versions of Oracle, there are ways to do something similar
using CREATE TABLE AS SELECT * FROM table1 WHERE data_col='data_you_need_to_keep';

Of course, it all depends on how much data you need to save. You don't say if this table has 101 million rows or 10,000 million rows.

HTH,
Nelson Petersen
Database Administrator
Home Hardware Stores Limited.
Nelson.Petersen_at_homehardware.ca

-----Original Message-----
From: Hatzistavrou John [mailto:John.Hatzistavrou.sema_at_mail.tellas.gr] Sent: Friday, January 30, 2004 7:48 AM
To: oracle-l_at_freelists.org
Subject: How to project the time a delete will take

Dear All,

Is there a formula that can give an estimation on how long a delete operation will take on a tables to delete 100 million rows?

Kind Regards,

Hatzistavrou Yannis

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jan 30 2004 - 08:25:24 CST