删除重复数据的方法测试

删除重复数据的办法:
method1:
rows= 428120729
TEMP 32G

结果:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Declare
  L_Cnt Number;
Begin
  Execute Immediate'alter session set nls_language=american';
  Insert Into tb_hxl_log Values('method1',Sysdate,Null);
  Commit;
  Delete
    From Tb_Hxl_User
   Where Rowid Not In
         (Select Min(Rowid)
            From Tb_Hxl_User a
           Group By A.Statedate, A.Usernumber, A.Provcode);
  Insert Into tb_hxl_log Values('method1',Sysdate,l_cnt);
  Commit;
End;

method2:
rows= 428120729
TEMP 32G
用时:2132.32s

Declare
  L_Cnt Number;
Begin
  Insert Into tb_hxl_log Values('method2',Sysdate,Null);
  Commit;
  Delete
  From Tb_Hxl_User T1
 Where Exists (Select 'x'
          From Tb_Hxl_User T2
         Where T2.Statedate = T1.Statedate
           And T2.Usernumber = T1.Usernumber
           And T2.Provcode = T1.Provcode
           And T2.Rowid > T1.Rowid);
  Insert Into tb_hxl_log Values('method2',Sysdate,l_cnt);
  Commit;
End;

method3
rows= 428120729
TEMP 32G
用时:4501s

Declare
  L_Cnt Number;
Begin
  Insert Into Tb_Hxl_Log Values ('method3', Sysdate, Null);
  Commit;
  Delete From Tb_Hxl_User a
   Where Rowid > (Select Min(Rowid)
                    From Tb_Hxl_User b
                   Where A.Statedate = B.Statedate
                     And A.Usernumber = B.Usernumber
                     And A.Provcode = B.Provcode);
  Insert Into Tb_Hxl_Log Values ('method3', Sysdate, L_Cnt);
  Commit;
End;