104-oracle大表删除重复记录的几种方法

--如果是多个字段去重,一起写到 group by 后面
select /*+ parallel(8) */
      sum(cnt) as total_cnt
     ,sum(cnt-1) as dup_cnt
from (select count(*) cnt from t1 group by object_name )
;

--如果是多个字段去重, 一起写到partition by 后面
delete 
   /*+ parallel(8) 
      opt_param('_hash_join_enabled' 'false') 
      opt_param('_optimizer_sortmerge_join_enabled' 'false') 
   */ 
from t1 
where rowid in 
(select rowid 
 from  
   (select rowid,row_number() over
          (partition by object_name 
           order by created desc nulls last) as rn 
    from t1) 
 where rn>1
);

delete /*+ enable_parallel_dml   parallel(8) */
from t1 
where rowid in 
(select rowid from 
    (select rowid,row_number() 
         over(partition by object_name order by created desc nulls last) rn 
     from t1
    ) where rn>1
) ;

--创建临时表分区表保存待删除rowid及对应的批次:
--ntile(10) 分析函数负责把记录拆分等10份, 相邻rowid分在一组
--建分区表是为了避免临时表的多次全表扫描
CREATE TABLE tmp_t1_rid parallel 8
PARTITION BY RANGE (batch_id) interval (1)
(  
  PARTITION p1 VALUES LESS THAN (2)   
)as
select ntile(10) over (order by rowid) as batch_id 
        , rowid as rid
from  (select rowid,row_number() over
               (partition by object_name order by created desc nulls last) rn 
       from t1
       ) 
where rn>1;


--指定不同的batch_id=1..10, 分别执行, 共10次:

--batch_id=1
delete from t1 where rowid in 
(select rid from tmp_t1_rid where batch_id=1);
commit;

...batch_id=2~9
commit;

--batch_id=10
delete from t1 where rowid in 
(select rid from tmp_t1_rid where batch_id=10);
commit;

create table t_nodup parallel 8
as 
select * from --这个*要替换成字段列表,否则新表会多一个rn字段
(select a.*,row_number() over 
   (partition by object_name order by created desc nulls last) as rn 
  from t1 a
) 
where rn=1;

--然后改表名:
  rename t1 to t1_bak;
  rename t_nodup to t1;

最后还要把原表上的约束,索引,grant等相关信息应用在新表上.

--并行相关hint可以酌情使用:
delete from t1 where rowid not in
(select max(rowid) from t1 group by object_name);