RE: Remove Duplicates

From: Alex Hillman <ahillman_at_erols.com>
Date: Wed, 12 Jun 2002 07:45:17 -0800
Message-ID: <F001.0047BE93.20020612074517@fatcity.com>

Ferenc, what happened with your web site - at least with Oracle version of it?

Alex Hillman
  -----Original Message-----
  From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Ferenc Mantfeld
  Sent: Tuesday, June 04, 2002 4:49 PM
  To: Multiple recipients of list ORACLE-L   Subject: RE: Remove Duplicates

  Tom. Replies below.
  Regards:
  Ferenc Mantfeld
  Senior Performance Engineer
  Siebel Performance Engineering
  Melbourne, 3000, VIC, Australia
  -----Original Message-----
  From: Terrian, Tom [mailto:tterrian_at_daas.dla.mil]   Sent: Tuesday, 4 June 2002 11:54 AM
  To: Multiple recipients of list ORACLE-L   Subject: Remove Duplicates

    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: Alex Hillman
  INET: ahillman_at_erols.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 Jun 12 2002 - 10:45:17 CDT