Suhen,
I have a similar problem at the moment although its 900 million rows of
which we want to delete approx. 200 million (they obviously never asked you
guys for help with their design!). Unfortunately we have just decided to
take the hit with downtime to correct the problem. We have scheduled 3 days
for downtime including taking a backup before doing anything. I would just
let it run, but notify the users that either they can expect performance
degredation if they continue to work while you delete, or lock them out from
the database for a while until it's finished running.
I don't think indexes will help you in this situation (although correct me
if I'm wrong anyone).
K.
-----Original Message-----
Sent: 12 September 2001 05:50
To: Multiple recipients of list ORACLE-L
List,
I need to delete duplicate records from a very large table (60 millions
records +).
There would be about 3 million duplicate entries.
What is the quickest way to do this?
The syntax that I am using is
delete from invaudee
where rowid not in (select min(rowid) from invaudee
group by audit_number);
This is taking a long time to run. I cannot see any entries in v$transaction
for the delete.
There is no indexes on the INVAUDEE table.
I created an index on the primary key column but it still takes forever to
run.
I do not have the space to CTAS.
Or should I write the duplicates to an EXCEPTIONS table and perform the
delete based on the entries
in the EXCEPTIONS table.
Any help would be greatly appreciated.
Suhen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas, Kevin
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
