Re: Deleting duplicates from 11 million records

Just a thought:
Can you use triggers to avoid duplicates? Then you don't have to run the script, and that's FAST.

D A

Suresh Bhat wrote:

> I have to delete duplicates from a 3 column table that has 11 million
> records.
> The table has index on columns TAG_DATE and TAG_NAME. The data in the
> table is for the past 60 days.
>
> The criterion for duplicates is having same TAG_DATE with timestamp and
> TAG_NAME.
>
> I am trying to use the time honored delete duplicates SQL script given
> below, but
> I have to do it by date ranges and it is too slow which has always been the
> case
> with this script.
>
> set doc off wrap on pause off
> /*
> The commented select statement below was for testing to make sure
> that I do have duplicates.
> select tag_name, to_date(tag_date,'dd-mon-yy hh24:mi:ss'), tag_value
> */
> delete
> from T outer
> where rowid > (select min(rowid)
> from T
> where tag_name = outer.tag_name
> and tag_date = outer.tag_date
> )
> and tag_date between to_date('01-may-98 00:00:00','dd-mon-yy
> hh24:mi:ss')
> and to_date('02-may-98
> 00:00:00','dd-mon-yy hh24:mi:ss')
> /
>
> Is there another faster way to do this either via SQL or PL/SQL. I would
> appreciate
> posting of any scripts you may have.
>
> Thank you very much.
>
> suresh.bhat_at_mitchell-energy.com
>
>
Received on Fri Jun 05 1998 - 01:20:28 CDT