Re: Deleting duplicates

From: Vikram Goel <vgoel_at_pts.mot.com>
Date: 1996/06/20
Message-ID: <4qbcri$9a9_at_lserv1.paging.mot.com>#1/1

Duane,

An easy way to select duplicates is the following: 1) Determine the uniqueness of each row, by a column or columns. 2) Then try to build a primary key on the column(s) with the exceptions into

    <EXCEPTIONS> clause. The creation of the special EXCEPTIONS table is in the    utlexpt.sql script in $ORACLE_HOME/rdbms/admin. e.g.

	alter table <table_name> add constraunt <index_name> (<column_list>
        using index ....
        exceptions into exceptions;

3) The exceptions table holds the rowid of the duplicate row(s). NOTE: If there are

    two rows only one of rows rowid, if three only two etc. 4) Use the value of the rowid in the exceptions table to delete the rows.

This is the fastest method to delete dups, as deletes are done using the rowid.

Hope this helps,

--
Vikram Goel                                
Sr. Oracle DBA - Consultant
Aerotek Inc.                                My email:  vgoel_at_emi.net

Motorola Info:                              Motorola email: vgoel_at_pts.mot.com
Mail Stop 39, Room S1014
1500 Gateway Blvd,
Boynton Beach, FL 33426 


In article <4q91mk$c07_at_srvr1.engin.umich.edu>, duanef_at_umich.edu (Any One) writes:


>gave any one give me some sample code to delete duplicates?  I have a table 


>that now contains duplicate data (due to unforseen circumstances) that I need 

>to clean up.

>

>Thanks,

>duanef_at_umich.edu

>

Received on Thu Jun 20 1996 - 00:00:00 CEST