Re: De-duping a table

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 7 Apr 2001 11:34:00 +0200
Message-ID: <9an873$4bqek$3@ID-62141.news.dfncis.de>

"Alan B" <Alan.B_at_opcomp.demon.co.uk> wrote in message news:986633055.23980.0.nnrp-10.9e985e86_at_news.demon.co.uk...
> Hi,
>
> I'm looking for a simple way to de-dupe a table based on primary key (I
> don't mind which dupe gets deleted).
>
> I've written some complex SQL which does an 8m row table in about 10 mins,
> but the following code which is much simpler disappears for hours.
>
> There are only a few duplicates in the table.
>
> delete from MY_TABLE
> where ROWID not in (select min(ROWID)
> from MY_TABLE
> group by MY_PRIMARY_KEY);
>
> Any suggestions would be appreciated.
>
> Regards,
> Alan
>
>

Two possible solutions
- (using Oracle 7.3.3 or higher, and cost based optimiser) add the hash_aj hint to the subquery
- (working in any version, any optimiser) delete from table x
where x.rowid =

          (select min(y.rowid)
           from table y
           where y.key = x.key)

Done the latter statement many times, and I'm sure it works.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Apr 07 2001 - 04:34:00 CDT