大表数据删除

需求:

几个关联的大表数据进行时间切割

注:  未按大表分区方法进行,理论上大表分区速度会更快

操作步骤:(建议业务低峰操作,并合理调整并行度及开关日志)

1. 为后面的操作提速,先将所有需要操作的表开启并行,并关闭日志

alter table a nologging; alter table a parallel(degree 8);

alter table b nologging; alter table b parallel(degree 8);

alter table c nologging; alter table c parallel(degree 8);

alter table d nologging; alter table d parallel(degree 8);

2.  为核对切割后的数据条数准确,先将3个大表的总数查询出来

SELECT COUNT(*) FROM a; -- 30,632,763
SELECT COUNT(*) FROM b; -- 27,315,078
SELECT COUNT(*) FROM c;  -- 5,070,113
SELECT COUNT(*) FROM d; -- 7,604,745

3.  利用CTAS创建备份表

CREATE TABLE a_bak as SELECT * FROM a where operatetime<date'2016-01-01';

--后面基于a_bak创建备份表,所以,先建个索引

CREATE INDEX ind_TMP01 ON a_bak(BTYPE, BID) nologging parallel 8 tablespace a_ind;

CREATE TABLE b_bak as SELECT * FROM b where (BTYPE,BID) IN (SELECT BTYPE,BID from a_bak);

CREATE TABLE c_bak as SELECT * FROM c where (BTYPE,BID) IN (SELECT BTYPE,BID from a_bak);

CREATE TABLE d_bak as SELECT * FROM d where (BTYPE,BID) IN (SELECT BTYPE,BID from a_bak);

4. 删除表数据前,一定要将原表上的索引设为不可用或删除,否则会大大影响速度

begin
for x in (SELECT * FROM user_indexes where table_name IN('A','B','C','D')) loop
  execute immediate 'ALTER INDEX '||x.index_name||' UNUSABLE';
end loop;
end;

5. 开四个session

每个session分别执行:

ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

alter session force parallel DML;

declare  
   cursor mycursor is SELECT ROWID FROM a WHERE (BTYPE,BID) IN (SELECT BTYPE,BID from a_bak) order by rowid; 
   type rowid_table_type is  table  of rowid index by pls_integer;
   v_rowid   rowid_table_type;
BEGIN
   open mycursor;
   loop
     fetch   mycursor bulk collect into v_rowid  limit 10000;   
     exit when v_rowid.count=0;
     forall i in v_rowid.first..v_rowid.last
        delete from a where rowid=v_rowid(i);
     commit;
   end loop;
   close mycursor;
END;

6. 收尾工作

核对删除后的表数据, SELECT (SELECT COUNT(*) FROM a)+(SELECT COUNT(*) FROM a_bak) 是否等于之前查询出来的总数

将原有的表恢复原并行度以开启日志

alter table a logging; alter table a parallel(degree default);

alter table b logging; alter table b parallel(degree default);

alter table c logging; alter table c parallel(degree default);

alter table d logging; alter table d parallel(degree default);

--重建索引 (如想快速建立索引,可以在后面加PARALLEL,但记得建完后改回noparallel)

declare
  STR VARCHAR2(400);
begin
  FOR x IN (SELECT TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME
                    FROM ALL_INDEXES
                   WHERE TABLE_NAME IN( 'A','B','C','D')
                     AND temporary = 'N') LOOP
    STR := 'ALTER INDEX ' || x.OWNER || '.' || x.INDEX_NAME ||
           ' REBUILD Tablespace '||x.TABLE_NAME||'_IND ';
    EXECUTE IMMEDIATE STR;
  END LOOP;
end;

--碎片整理

--查询位于HWM之下的数据大小

SELECT TABLE_NAME, 
         (BLOCKS * 8192 / 1024 / 1024) - 
         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
    FROM USER_TABLES 
   WHERE table_name = 'A';

执行:

 alter table A move; 
 exec dbms_stats.gather_table_stats('NJSX','A',CASCADE=>TRUE);

然后再查询下上面的SQL,对比下位于HWM之下的数据

--重新收集统计信息

begin
  for x in (SELECT TABLE_NAME
              FROM USER_TABLES
             WHERE TABLE_NAME IN ('A','B','C','D')) loop
  
    execute immediate 'BEGIN dbms_stats.gather_schema_stats(''TESTUSER'','''||x.TABLE_NAME||'''); end;';
  end loop;
end;

4个表共7000多万数据,3个多小时删除完成