Deleting duplicates from 11 million records

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Thu, 04 Jun 1998 13:44:59 GMT
Message-ID: <01bd8fce$8edfaba0$a504fa80@mndnet>

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 Thu Jun 04 1998 - 08:44:59 CDT