高效删除大表重复记录的解决方案

Oracle DDL与数据迁移

最新推荐文章于 2021-06-09 11:05:33 发布

原创最新推荐文章于 2021-06-09 11:05:33 发布 · 327 阅读

0 ·

CC 4.0 BY-SA版权

ORA-Ops 专栏收录该内容

21 篇文章

订阅专栏

本文详细介绍了一种在Oracle数据库中使用DDL语句创建索引、中间表，并进行数据迁移的方法。通过创建结构一致的中间永久表来节省资源，接着进行数据的筛选与合并，最后完成数据迁移至目标表的过程。

---ddl before
CREATE bitmap index BIT_IDX_DATA_SRC on t1(DATA_SRC) NOLOGGING;
create index IDX_ID_Number on t1(ID_NUMBER) NOLOGGING;
ANALYZE INDEX BIT_IDX_DATA_SRC COMPUTE STATISTICS;
ANALYZE INDEX IDX_ID_Number COMPUTE STATISTICS;



--创建结构一致的中间永久表(DDL无undo非常节省资源)IN ('CBRC', 'CBRC File')  ----非常关键的一步
CREATE TABLE t1_mid tablespace TBS_LS_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )  AS  SELECT * 
    FROM t1 t1
   WHERE t1.record_id = (SELECT MAX(t2.record_id)
                            FROM t2 t2
                           WHERE t2.data_src IN ('CBRC', 'CBRC File')
                            AND t1.id_number = t2.id_number)                        
     AND t1.data_src IN ('CBRC', 'CBRC File');

--NOT IN ('CBRC', 'CBRC File')
INSERT INTO t1_mid SELECT * FROM blacklist a WHERE a.data_src NOT IN ('CBRC', 'CBRC File');
COMMIT;


-----ddl after
    
TRUNCATE TABLE t1;
DROP INDEX BIT_IDX_DATA_SRC;
DROP INDEX IDX_ID_Number;

--移动数据从中间表到最终表
INSERT INTO t1 SELECT * FROM blacklist_mid;

/*INSERT INTO t1 select * From (SELECT * FROM blacklist_mid  order by 1) where rownum<=1000000;*/
ANALYZE TABLE t1 COMPUTE STATISTICS; --收集统计信息

--清理环境
DROP TABLE t1_mid;