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 !) ?

If you want to browse the duplicate rows before deciding which ones to delete, you can do a GROUP BY on the key columns with HAVING COUNT(*) > 1. Example:

SELECT	*
FROM	MYTABLE
WHERE	(KEY1, KEY2, ... KEYn) IN
	(
	SELECT	KEY1, KEY2, ... KEYn
	FROM	MYTABLE
	GROUP	BY KEY1, KEY2, ... KEYn
	HAVING	COUNT(*) > 1
	)
ORDER	BY KEY1, KEY2, ... KEYn ;

If the subquery returns a large number of rows then the IN operator may well be slow, depending on which indexes exist on KEY1, KEY2, ... KEYn.

Hope this helps.

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE