Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: De-duping a table

Re: De-duping a table

From: Alan B <Alan.B_at_opcomp.demon.co.uk>
Date: Sat, 7 Apr 2001 17:36:26 +0100
Message-ID: <986661781.6241.0.nnrp-10.9e985e86@news.demon.co.uk>

I think that this will delete the records which do not have duplicates. "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:9an873$4bqek$3_at_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 - 11:36:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US