Re: Identify duplicate keys

In article <C4o9C6.8AC_at_da_vinci.it.uswc.uswest.com>, Jeffrey L. DeMent <jld_at_advtech.uswest.com> writes:
|> Trying to create an index on an Oracle V6 table -- but it's failing
|> because of duplicate keys. Is there a slick/fast/easy way to identify
|> the rows with duplicate keys (for subsequent deleting !) ?
|>
|> Thanks.

There have been many replies suggesting SQL queries based on GROUP BY. This is fine if you have only a thousand or so key values. However, if you have hundreds of thousands, then the GROUP BY mechanism will require an enormous amount of temporary sort space.

Another way to accomplish the result without any temporary space is as follows:

    SELECT A.ROWID,A.KEY,B.ROWID
    FROM [tablename] A, [tablename] B
    WHERE A.key-1 = B.key-1
    ...
    AND A.key-n = B.key-n
    AND A.ROWID != B.ROWID Note that this does depend on the existence of a non-unique index on at least one key column in order to run in a reasonable amount of time.

Glenn Goodrum
Sierra Geophysics
ggoodrum_at_sierra.com Received on Mon Apr 05 1993 - 17:06:07 CEST