Oracle-L: Remove Duplicates

From: Terrian, Tom <tterrian_at_daas.dla.mil>
Date: Tue, 04 Jun 2002 10:53:48 -0800
Message-ID: <F001.00473E09.20020604105348@fatcity.com>

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

alter table &table_name

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

How to find duplicates:

select &column_name, count(&column_name)

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

Tom

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Terrian, Tom
INET: tterrian_at_daas.dla.mil

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 - 13:53:48 CDT