RE: help with deleting duplicate records from very large table

From: Thomas, Kevin <Kevin.Thomas_at_calanais.com>
Date: Wed, 12 Sep 2001 00:40:05 -0700
Message-ID: <F001.0038AFAF.20010912003018@fatcity.com>

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).

-----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: Kevin.Thomas_at_calanais.com

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: ListGuru_at_fatcity.com (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). Received on Wed Sep 12 2001 - 02:40:05 CDT