Have you tried querying V$SESSION_LONGOPS ? {It requires TIMED_STATISTICS=TRUE and the table having been analyzed} Hemant At 04:42 PM 30-01-04 +0200, you wrote: >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 //www.freelists.org/archives/oracle-l/ >FAQ is at //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 //www.freelists.org/archives/oracle-l/ >FAQ is at //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 //www.freelists.org/archives/oracle-l/ >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html >----------------------------------------------------------------- Hemant K Chitale Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 24-Jan-04} ---------------------------------------------------------------- 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 //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------