Re: Deleting duplicates from 11 million records

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Fri, 05 Jun 1998 19:18:42 GMT
Message-ID: <01bd90c6$a5248400$a504fa80@mndnet>

Hi !!

Thanks for the input and the thoughts. What I was expecting was some kind of script that one may already had. Here is what I ended up writing. It took
about 23 minutes to insert the distinct rows into the new tabel on the Sun UNIX
machine:

create or replace procedure delete_duplicates

    is
BEGIN
/*

   STEPS:

     --
     1. Insert DISTINCT rows into TABLE_NAME_TEMP from TABLE_NAME,
        which this procedure accomplishes.
     --
     2. Then in SQL*Plus either RENAME TABLE_NAME to TABLE_NAME_TEMP
        or DROP it.  Drop the index on TABLE_NAME if present.
     --
        Rename TABLE_NAME_TEMP to TABLE_NAME and create a unique index
        on TAG_DATE, TAG_NAME to avoid the duplicate insertion in future.

*/
DECLARE
   min_tag_date date;
--

   cursor curs_min_tag_date

       is
   select min(tag_date)
     from table_name;
--

BEGIN
/*

   Insert DISTINCT records from table TABLE_NAME into    TABLE_NAME_TEMP.
*/

   BEGIN

      open    curs_min_tag_date;
      fetch   curs_min_tag_date
       into   min_tag_date;
      close   curs_min_tag_date;


--


      dbms_output.put_line
         ('Min TAG_DATE from table TABLE_NAME: '
          ||to_char(min_tag_date,'dd-MON-yy hh24:mi:ss')
         );


--


      if min_tag_date is not null then
         /*
            This will take care of duplicates for up to 80 days.
         */
         for j in 1..80   loop
            insert
              into   table_name_temp
            select   distinct *
              from   table_name
             where   tag_date between trunc(min_tag_date    )
                                  and trunc(min_tag_date + 1);
            dbms_output.put_line
               ('Loop: '||j||', Inserting records for day: '
                 ||to_char(min_tag_date,'dd-MON-yy hh24:mi:ss')
               );
            commit;
            min_tag_date := min_tag_date + 1;
         end loop;
      end if;
   EXCEPTION
      when others then
        null;

   END;
END;
END;
/

Suresh Bhat <suresh.bhat_at_mitchell-energy.com> wrote in article <01bd8fce$8edfaba0$a504fa80_at_mndnet>...
> I have to delete duplicates from a 3 column table that has 11 million > records.

snip

> 
> 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 - 14:18:42 CDT