RE: How to project the time a delete will take

Yannis,

Do you have a test database and system?
How many rows will be left in the table when you're done?

It's the usual problem of trying to predict something based on a single
event.
Reality might be completely different than your prediction.

By my arithmetic, 200 million rows at 7.32 seconds per row is 406,667
hours???  <big grin>
Life is too short to spend 46+ years on this!

Modify your experiment.  You should delete at least 100 rows between your
get_time calls.
That will produce a much more meaningful elapsed time.  If possible, delete
10,000 or 1 million rows.
You have a much better chance that your predicted elapsed time for 200
million rows will be close
to reality.

When you delete the 200 million rows, if you have exclusive use of the
table, 
you might consider dropping the indexes before the delete and re-creating
them later.
That saves updating the index blocks after each delete, but adds the time to
re-create
the indexes afterwards.

Partitions would help, but that's a separate project.
I'm sure there are others on this list who have more experience with
deleting this volume of data.

Nelson

-----Original Message-----
From: Hatzistavrou John [mailto:John.Hatzistavrou.sema@xxxxxxxxxxxxxx]
Sent: Friday, January 30, 2004 9:43 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: How to project the time a delete will take


Dear Nelson,

Oracle release 8.1.7.4, solaris 8=20
The entries that will be deleted are ~ 200 million . The customer is
pushing for delete although we have pointed out that a partition will be
wiser (we are using truncate now). The customer asked us to prove that
the delete is not efficient although we have explained that there are a
number of issues such as redo and undo etc. There are 3 indexes defined
against the table.
I am doing the following experiment
a) get a reading from dbms_utility.get_time
b) execute a delete for a row
c) get a second reading from dbms_utility.get_time
d) Check the diff between a and c which is 732 centiceconds (if I
convert it well is 7,32 secs)

Kind Regards,


Hatzistavrou Yannis



-----Original Message-----
From: nelson.petersen@xxxxxxxxxxxxxxx
[mailto:nelson.petersen@xxxxxxxxxxxxxxx]=20
Sent: Friday, January 30, 2004 4:25 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: How to project the time a delete will take

Hi John (Yannis?),

Assumptions:

1.  You are deleting from one table.
2.  There are no referential constraint issues.  (You have disabled or
dropped them.)
3.  You are deleting 80% or more of the data from this table. =20
    (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=3D"WHERE ..."  file=3Ddata_to_be_left.dmp    etc.
>>>   The WHERE clause should select the data that you do *NOT* want to
delete.

Truncate the table.
IMPORT file=3Ddata_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=3D'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@xxxxxxxxxxxxxxx



-----Original Message-----
From: Hatzistavrou John [mailto:John.Hatzistavrou.sema@xxxxxxxxxxxxxx]
Sent: Friday, January 30, 2004 7:48 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: How to project the time a delete will take


Dear All,
=20

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?

=20

Kind Regards,

=20

=20

Hatzistavrou Yannis

=20


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: