多条件删除数据_Oracle 大数据表清理迁移方案

最新推荐文章于 2021-04-08 18:36:26 发布

weixin_39999781

最新推荐文章于 2021-04-08 18:36:26 发布

阅读量255

点赞数

文章标签：多条件删除数据

本文链接：https://blog.csdn.net/weixin_39999781/article/details/112627834

版权

背景

为了应对系统投产两年多时间内，数据库中数据量不断增多。系统运行效率性能在海量不相关历史数据的影响下有下降风险。为提高系统运行效率，定时将有效期外（例如一年）的数据视为历史数据，迁移至历史表空间，同时清除当前库数据，或者采用数据归档入数仓后直接删除。

方案目的

历史数据完整迁移
正式库数据安全删除
迁移所涉及表可配置（增量、全量）
可手工操作、可定时任务(JOB)，对于操作结果需要记录，方便审计及查询记录

方案概要

整个方案通过将源表（需要做数据清理的表）满足迁移条件的数据移动到历史表的方式来清理大表数据，所有历史表建立统一的历史表空间和用户。同时为了满足通用性，采用配置的方式来配置需要迁移的表及清理条件。同时将清理的结果记录到清理日志表。方便事后完整性验证。

迁移方式采用job+存储过程形式进行，由job定时拉起存储过程进行数据迁移，第一次迁移时采用手动的方式，此后每日增量数据采用定时任务进行自动清理。

迁移逻辑

建立历史用户和历史表空间，赋予历史用户对源表的SELECT、DELETE操作权限。这个工作可以在清理前完成，作为前置条件

select 'GRANT ALL ON  源表用户.'||table_name||' to 清理历史用户;'  from all_tables where lower(owner)='源表用户';

在历史表空间中创建需要迁移的历史表（分区表，方便历史表归档），表名规则为，源表名_BAK
在历史表空间中创建迁移配置表和清理日志表迁移配置表：迁移配置表中一条记录就是一张表清理条件，包含如下一些字段

将需要清理表的数据插入到该表。

清理日志表：清理日志表记录清理的结果，方便查询历史清理情况及做完整性校验

将每张表的清理情况记录到该表中

4. 查询迁移配置表执行清理。迁移配置表包括源表名、目标表名(也就是历史表名，规则为在源表名后面加_bak)、迁移条件(筛选清理数据条件)、迁移类型（全量、增量）、每次处理页大小（分页和批量提交大小）、清理标志位（根据筛选清理条件物理删除数据）

5. 数据迁移和清理，需要保证在同一个事务中，通过控制page_size大小控制事务的大小

如果“清理标志位”字段为2，直接跳过
如果是全量删除，需要先清空历史表
使用历史用户根据迁移条件分页查询源表数据
按page_size将数据插入历史表
根据“清理标志位”字段判断进行源表清理page_size条数据，跟插入在同一个事务中

6. 删除数据逻辑

非分区表：根据要删除的表清理条件查询出rowid，删除时根据rowid+表清理条件作为删除条件进行删除分批提交；

--示例代码
declare  
    --按ROWID排序的Cursor，删除条件是XXX=XXXX，根据实际情况来定。
   cursor mycursor is SELECT  主键或唯一键列名 FROM 源表 WHERE  XXX=XXXX  order by rowid;   
   v_del_conditon xx;
BEGIN
   open mycursor;
   loop
     --每次处理5000行，也就是每5000行一提交
     fetch   mycursor bulk collect into v_del_conditon  limit 5000;  
     exit when v_del_conditon.count=0;
     forall i in v_rowid.first..v_rowid.last
        delete from test where rowid = v_del_conditon (i) and XXX=XXXX(此处为上面的查询条件);
     commit;

分区表：根据分区初始日期-数据保留天数得出要删除的分区区间，然后查询出所有分区，如果判断该分区在此区间则进行删除

--示例代码
 for x in (select * from dba_tab_partitions where       table_name=upper(tab_name)) loop
      /** 将分区的high_value赋值到partition_date */
      execute immediate 'select '|| x.high_value || 'from dual' into partition_date;
      /** partition_date在开始时间和结束时间之间的分区drop掉，并更新全局索引*/
      if cur_date < partition_date and partition_date <= end_date then
          v_drop_sql := 'ALTER TABLE ' || orig_table_owner || '.' || tab_name || ' DROP PARTITION ' || x.partition_name || ' update global indexes';
          execute immediate v_drop_sql;
       end if;
    end loop;

以上方案完整脚本通过关注公众号「陈言懒调」发送“大表清理”获取；脚本中包含了创建表、创建清理存储过程、创建公共函数（打印日志）等。