Re: Find out duplicate keys

In article <michel.lalonde.68.000A7E5B_at_sit.ulaval.ca>,

   michel.lalonde_at_sit.ulaval.ca (Michel Lalonde) wrote:
>In article <4k8i2k$qs_at_amanda.dorsai.org> vkwan_at_dorsai.org (Vito Kwan) writes:
>>From: vkwan_at_dorsai.org (Vito Kwan)
>>Subject: Find out duplicate keys
>>Date: 7 Apr 1996 10:05:08 -0400
 

>>Hi all,
>> I wonder if there is a easier way to delete the rows with duplicate
>>keys (including single PK and composite PKs) without using resursion or
>>alias of the table.
>> Thanks for your help.
 

>>Vito Kwan
>>vkwan_at_dorsai.org
 

>>--
>>Vito Kwan
>>vkwan_at_dorsai.org
>>======================================================================
>>I am Sir Oracle, And when I ope my lips, let no dog bark!
>> Shakespeare: The Merchant of Venice
>
>Enable the PK with the EXCEPTIONS clause. The rowids of the duplicate will be
>in the exceptions table. Then you:
>
>delete from the_table
>where rowid in (select row_id from exeptions_table);
>
>Miclel Lalonde

The problem with this syntax is that it will delete all the duplicates not just one of them. If a key appears twice, then both occurrences will be removed. I had to write a PL/SQL routine to skip the first occurrence in the exception table in order to do it successfully.

The query by rowid on the original table was too slow since it performs a full table scan for each row. Received on Thu Apr 18 1996 - 00:00:00 CEST