Re: How to find Duplicate records

After looking at the faq referenced below, I had the following questions:

(1) What does the following sql do with the /* +APPEND */ notation?

"SQL> create table new_test nologging
  2 as (select /* +APPEND */ distinct * from test);

(2) In trying to improve the performance of the delete operation, would it help if a (nonunique) index were first constructed on the columns that were not unique prior to trying to delete the duplicate rows? I suspect this would help with the deletion and would be used by the unique constraint when it could be validated. In general, I wonder if a rule of thumb might be to first create an index on columns that one desires to be unique and then try to enable a unique constraint afterwards?

Frank Hubeny

Norman Dunbar wrote:

> The advice below will delete the duplicates ok, but it will also delete
> the rows you (probably) want to keep as well !
> When the exceptions table is used, Oracle doesn't know which of the
> duplicated rows you need/want to keep, so it puts them all into the
> exceptions table.
>
> Be afraid, be very afraid :o)
>
> Norman.
>
> PS. Check out http://www.jlcomp.demon.co.uk/faq/duplicates.html for full
> details.
>
> ------------------------------------------------------------------------
> --------
> Norman Dunbar EMail: NDunbar_at_LynxFinancialSystems.co.uk
> Database/Unix administrator Phone: 0113 289 6265
> Lynx Financial Systems Ltd. Fax: 0113 201 7265
> URL: http://www.LynxFinancialSystems.com
> ------------------------------------------------------------------------
> --------
>
> -----Original Message-----
> From: Yaroslav Perventsev [mailto:p_yaroslav_at_cnt.ru]
> Posted At: Tuesday, July 24, 2001 6:55 AM
> Posted To: server
> Conversation: How to find Duplicate records
> Subject: Re: How to find Duplicate records
>
> <SNIP>
>
> After this manipulation you'll have table exception with rowids of
> duplpicate records and you may delete them:
>
> delete from YOUR_10_MILLION_TABLE
> where rowid in (select rowid from exception where
> table_name=YOUR_10_MILLION_TABLE)
Received on Thu Jul 26 2001 - 01:10:17 CDT