RE: Remove Duplicates

[David Wagoner]

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 [EMAIL PROTECTED] Fax (919) 466-6783 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----- From: Terrian, Tom [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 04, 2002 2:54 PM 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); 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