Tom_anowser之一

You Asked 
Tom,

I am interested in finding the fastest way to save off
the duplicate records to a duplicates table.  My source
table has about 13 million rows and the users want to remove
all duplicates from the primary table and store them in a
duplicates table for later review.  (I know, not exactly the
smartest thing to do.  However, I have not control over this).
I expect there to be a relatively low number of dups.
(less than 1%).  Here is what I have come up with.

create table dup_rowids
as
(select rowid
from table_a
minus
select Max(rowid)
from table_a
      group by A, B, C);

Using this list, I can then create table for those records
that are dups and those records that are unique

Am I on the right track?

Thanks,
Jim 


and we said...
how about adding a unique constraint on the columns in question and using "exceptions 
into".  For example:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( a int, b int, c int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec gen_data( 'T', 500 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select * from t where rownum < 10;

9 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table exceptions(row_id rowid,
  2                          owner varchar2(30),
  3                          table_name varchar2(30),
  4                          constraint varchar2(30));

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_unique unique(a,b,c) 
exceptions into exceptions;
alter table t add constraint t_unique unique(a,b,c) exceptions into exceptions
                             *
ERROR at line 1:
ORA-02299: cannot validate (OPS$TKYTE.T_UNIQUE) - duplicate keys found


ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table dups
  2  as
  3  select *
  4    from t
  5   where rowid in ( select row_id from exceptions )
  6  /
create table dups
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where rowid in ( select row_id from 
exceptions );

18 rows deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select distinct * from dups;

9 rows created.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25380220/viewspace-697572/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25380220/viewspace-697572/