Oracle-L: RE: Remove Duplicates

From: David Wagoner <dwagoner_at_arsenaldigital.com>
Date: Wed, 05 Jun 2002 10:13:33 -0800
Message-ID: <F001.0047559E.20020605101333@fatcity.com>

Here is an interesting script I found on Metalink (Note:1019920.6) for removing duplicates, but I have not tried it yet:

Title:

Script to Eliminate Non-unique Rows

Disclaimer:

This script is provided for educational purposes only. It is NOT supported by
Oracle World Wide Technical Support. The script has been tested and appears

to work as intended. However, you should always test any script before relying on it.

PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text

editors, email packages and operating systems handle text formatting
(spaces,

tabs and carriage returns), this script may not be in an executable state when
you first receive it. Check over the script to ensure that errors of this type are corrected.

Abstract:

This script removes all but one row (all but the row with the highest rowid)

from <owner>.<table> in each group of rows having identical values in <column(s)>. Multiple columns must be separated with commas (without spaces).

Script TFSUNIQU is intended primarily for use in deleting rows that prevent the creation of a unique index on the columns in <column(s)>. It will happily delete rows that are not identical, as long as the rows are identical with respect to the values of the columns in <column(s)>.

Requirements:

You must have DELETE privileges on the selected table.

Script:

cut ---------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: TFSUNIQU.SQL
REM USAGE:"@path/tfsuniqu schema_name table_name column_name(s)"

REM ------------------------------------------------------------------------

REM REQUIREMENTS:
REM DELETE on selected table

REM ------------------------------------------------------------------------

REM AUTHOR:  
REM    Grant Franjione, Phil Joel, and Cary Millsap      
REM    (c)1994 Oracle Corporation      
REM ------------------------------------------------------------------------

REM PURPOSE:
REM Removes all but one row (all but the row with the highest rowid) REM from <owner>.<table> in each group of rows having identical values REM in <colum(s)>. Multiple columns must be seperated with commas REM (without spaces).
REM
REM TFSUNIQU is intended primarily for use in deleting rows that REM prevent the creation of a unique index on the columns in REM <column(s)>. It will happily delete rows that are not identical, REM as long as the rows are identical with respect to the values of REM the columns in <column(s)>.

REM ------------------------------------------------------------------------

REM EXAMPLE:
REM N/A

REM ------------------------------------------------------------------------

REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially.

REM ------------------------------------------------------------------------

REM Main text of script follows:

def owner      = &&1 
def table      = &&2 
def uukey      = &&3

delete from &owner..&table
where rowid in (
  select rowid from &owner..&table
  minus
  select min(rowid) from &owner..&table group by &uukey )
/

undef owner
undef table
undef uukey

cut ---------------------- cut -------------- cut --------------

David B. Wagoner
Database Administrator
Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110
Cary, NC 27511-8582
Office (919) 466-6723
Pager 8666864767_at_archwireless.net
Fax (919) 466-6783
<http://www.arsenaldigital.com/> http://www.arsenaldigital.com/

NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you.

-----Original Message-----
Sent: Tuesday, June 04, 2002 2:54 PM
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);
Or
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: David Wagoner
  INET: dwagoner_at_arsenaldigital.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 05 2002 - 13:13:33 CDT