RE: Remove Duplicates

From: Ferenc Mantfeld <fmantfeld_at_siebel.com>
Date: Tue, 04 Jun 2002 12:48:44 -0800
Message-ID: <F001.004741ED.20020604124844@fatcity.com>

Tom. Replies below.

Regards:
Ferenc Mantfeld
Senior Performance Engineer
Siebel Performance Engineering
Melbourne, 3000, VIC, Australia
-----Original Message-----
Sent: Tuesday, 4 June 2002 11:54 AM
To: Multiple recipients of list ORACLE-L

I know I have seen this posted before.......  

We have a large range partitioned table that has duplicates in it. What is the fastest way to remove the dups.? I have the following scripts which do it but may be fast or slow. What do you guys use?

DELETE FROM tablename
WHERE ROWID NOT IN
  (SELECT MIN(ROWID)
    FROM tablename
    GROUP BY fieldnames);
[Ferenc Mantfeld] This will be your fastest way, provided you have an index
on the columns searched for. Actually the format of the statement would be  

delete from INVOICE_DETAILS A where A.rowid >

(select min(rowid) from INVOICE_DETAILS B where

    B.INV_NUM=A.INV_NUM and B.LINE_NUM=A.LINE_NUM ) ;  

Ensure you have a composite index on INVOICE_DETAILS (INV_NUM, LINE_NUM).

Or

alter table &table_name

       add constraint duplicate_cons 
       unique key (&column_name) 
         exceptions into exception table;

[Ferenc Mantfeld] Problem with this is when you want to delete the
duplicates, you have no way of telling, unless you code the min function again. If you have triplicates, and want to keep one of them and blow away the other two, this is a tedious way, and all this does is to help you identify the duplicates.

How to find duplicates:

select &column_name, count(&column_name)

         from &table_name 
         group by &column_name 
       having count(&column_name) > 1; 

[Ferenc Mantfeld] Same as above. only identifies the duplicates, does
nothing to remove them.  

Tom  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ferenc Mantfeld
  INET: fmantfeld_at_siebel.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 Tue Jun 04 2002 - 15:48:44 CDT