Oracle删除大表数据慢,大表删除数据特别慢的sql（修改：重复记录只有50条）

再看看tom提供的这个方法，也不错

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.