Re: Need alternate way of eliminating duplicates from 93K table

From: Saad Ahmad <sahmad_at_mfa.com>
Date: 1996/04/09
Message-ID: <4kccf3$q2f_at_homer.alpha.net>#1/1


Jeff Perry (jp_at_vllyoak.resun.com) wrote:
> Here's my situation; a user discovered their table
> with 93K rows has duplicated data, and they want
create an index on table's primary key
then:
delete from table_A a

       where exists ( select 'x'
                      from   table_A b
                      where  
                             b.primary_key = a.primary_key and
                             b.rowid      >  a.rowid
                    );


> First, I attempted a correlated subquery as follows:
> delete from ciim86_oeorder_history A
> where rowid>
> (select min(rowid)
> from ciim86_oeorder_history
> where field1 = A.field1
> and field2 = A.field2
> );
> Thirty six (36) hours later, this query was still
> running when the system was rebooted.
Did you have an index on the table here ???

If you want to use PL/SQL, just put null in the exception clause for DUP_VAL_ON_INDEX exception.

--
**************************************************************
*                          Saad Ahmad                        *
*                          Senior Software Engineer          *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-7457               *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************
Received on Tue Apr 09 1996 - 00:00:00 CEST

Original text of this message