实例讲解临时处理去重 80w 数据时夯死现象

SQL> select count (*) from tb_bj_banker_etl;

2552381

SQL> select count (*) from tb_bj_banker_etl where (id) in ( select id from tb_bj_banker_etl group by id having count (*)>1) and rowid not in ( select max (rowid) from tb_bj_banker_etl group by id having count (*)>1);

830099

SQL> delete from tb_bj_banker_etl where (id) in ( select id from tb_bj_banker_etl group by id having count (*)>1) and rowid not in ( select max (rowid) from tb_bj_banker_etl group by id having count (*)>1);

SQL> commit ;

SELECT

  A.OWNER,            --OBJECT所属用户

  A.OBJECT_NAME,         --OBJECT名称

  B.XIDUSN,

  B.XIDSLOT,

  B.XIDSQN,

  B.SESSION_ID,          --锁表用户的session

  B.ORACLE_USERNAME,       --锁表用户的Oracle用户名

  B.OS_USER_NAME,         --锁表用户的操作系统登陆用户名

  B.PROCESS,

  B.LOCKED_MODE,

  C.MACHINE,           --锁表用户的计算机名称

  C.STATUS,            --锁表状态

  C.SERVER,

  C.SID,

  C.SERIAL#,

  C.PROGRAM            --锁表用户所用的数据库管理工具

FROM

  ALL_OBJECTS A,

  V$LOCKED_OBJECT B,

  SYS.GV_$SESSION C

WHERE

  A.OBJECT_ID = B.OBJECT_ID

  AND B.PROCESS = C.PROCESS

ORDER BY 1,2

第一次：

delete from tb_bj_banker_etl where (id) in ( select id from tb_bj_banker_etl group by id having count (*)>1) and rowid not in ( select max (rowid) from tb_bj_banker_etl group by id having count (*)>1) and rownum<=100000;

commit ;

第二次：

delete from tb_bj_banker_etl where (id) in ( select id from tb_bj_banker_etl group by id having count (*)>1) and rowid not in ( select max (rowid) from tb_bj_banker_etl group by id having count (*)>1) and rownum<=100000;

commit ;

。。。。。。。

。。。。。。。

。。。。。。。

第八次：

delete from tb_bj_banker_etl where (id) in ( select id from tb_bj_banker_etl group by id having count (*)>1) and rowid not in ( select max (rowid) from tb_bj_banker_etl group by id having count (*)>1);

commit ;