Oracle-使用切片删除的方式清理非分区表中的超巨数据-阿里云开发者社区

概述

大表中海量历史数据的更新与删除一直是一件非常头痛的事情，在表已经分区的前提下我们还可以利用并行或者truncate parition等手段来为UPDATE或者DELETE提速，但是如果对象是普通的非分区对表(non-partitioned heap table)的话，似乎就没有太好的加速方法了， nologging或parallel 对非分区表都没有效果。

Step1:rowid_chunk.sql

将下面的SQL保存为 rowid_chunk.sql文件

主要功能：将表按照rowid范围分区，获得指定数目的rowid Extent区间(Group sets of rows in the table into smaller chunks)，以便于非分区表利用rowid来实现并行删除或更新

REM  rowid_ranges should be at least 21
REM  utilize this script help delete large table
REM  if update large table  Why not online redefinition or CTAS
-- This script spits desired number of rowid ranges to be used for any parallel operations.
-- Best to use it for copying a huge table with out of row lob columns in it or CTAS/copy the data over db links.
-- This can also be used to simulate parallel insert/update/delete operations.
-- Maximum number of rowid ranges you can get here is 255.
-- Doesn't work for partitioned tables, but with minor changes it can be adopted easily.
-- Doesn't display any output if the total table blocks are less than rowid ranges times 128.
-- It can split a table into more ranges than the number of extents
set verify off
undefine rowid_ranges
undefine segment_name
undefine owner
set head off
set pages 0
set trimspool on
select 'where rowid between ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0) ||''' and ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999) || '''' ||';'
  from (select distinct b.rn,
                        first_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,
                        last_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,
                        first_value(decode(sign(range2 - range1),
                                           1,
                                           a.bid +
                                           ((b.rn - a.range1) * a.chunks1),
                                           a.bid)) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,
                        last_value(decode(sign(range2 - range1),
                                          1,
                                          a.bid +
                                          ((b.rn - a.range1 + 1) * a.chunks1) - 1,
                                          (a.bid + a.blocks - 1))) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2
          from (select fid,
                       bid,
                       blocks,
                       chunks1,
                       trunc((sum2 - blocks + 1 - 0.1) / chunks1) range1,
                       trunc((sum2 - 0.1) / chunks1) range2
                  from (select /*+ rule */
                         relative_fno fid,
                         block_id bid,
                         blocks,
                         sum(blocks) over() sum1,
                         trunc((sum(blocks) over()) / &&rowid_ranges) chunks1,
                         sum(blocks) over(order by relative_fno, block_id) sum2
                          from dba_extents
                         where segment_name = upper('&&segment_name')
                           and owner = upper('&&owner'))
                 where sum1 > &&rowid_ranges) a,
               (select rownum - 1 rn
                  from dual
                connect by level <= &&rowid_ranges) b
         where b.rn between a.range1 and a.range2) c,
       (select max(data_object_id) oid
          from dba_objects
         where object_name = upper('&&segment_name')
           and owner = upper('&&owner')
           and data_object_id is not null) d
           /

利用该脚本可以获取到这些分割后的区间块的起始rowid和结尾rowid，之后利用between start_rowid and end_rowid的条件构造多条DML语句，因为这些DML语句所更新的数据都是在互不相关的区间内的，所以可以在多个终端内并行地运行这些DML语句，而不会造成锁的争用或者Oracle并行执行协调(Parallel Execution coordinator ) 所带来的一些开销。

Step2:文件上传到Oracle主机用户，执行@rowid_chunk.sql

Step2.1: 上传脚本

使用oracle用户登录主机，上传到目录。我这里上传到了/oracle目录下

Step2.2: 连接数据库，获取分片

使用oracle用户登录主机，在/oracle目录下通过sqlplus登录

如果数据量过大，可以分片多一些，少量多次删除

artisandb:[/oracle$]pwd
/oracle
artisandb:[/oracle$]sqlplus artisan/artisan2018@TB  -- 用户名密码以及tns的名字
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 31 16:08:37 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @rowid_chunk.sql
Enter value for rowid_ranges: 10 --输入分片个数
Enter value for segment_name: XXXXX -- 输入要操作的表名
Enter value for owner: YYYY-- 输入用户名
where rowid between 'AAAYHtAAGAAAAEAAAA' and 'AAAYHtAALAAAx5yCcP';
where rowid between 'AAAYHtAALAAAx5zAAA' and 'AAAYHtAANAAA75yCcP';
where rowid between 'AAAYHtAANAAA75zAAA' and 'AAAYHtAAOAACQPyCcP';
where rowid between 'AAAYHtAAOAACQPzAAA' and 'AAAYHtAAVAABJTyCcP';
where rowid between 'AAAYHtAAVAABJTzAAA' and 'AAAYHtAAWAAA7ryCcP';
where rowid between 'AAAYHtAAWAAA7rzAAA' and 'AAAYHtAAYAAA6hyCcP';
where rowid between 'AAAYHtAAYAAA6hzAAA' and 'AAAYHtAAaAACRLyCcP';
where rowid between 'AAAYHtAAaAACRLzAAA' and 'AAAYHtAAcAAAYpyCcP';
where rowid between 'AAAYHtAAcAAAYpzAAA' and 'AAAYHtAAiAACGnyCcP';
where rowid between 'AAAYHtAAiAACGnzAAA' and 'AAAYHtAAjAAA/xyCcP';
10 rows selected.
SQL>

相当于人为的将一张非分区表划分成输入的【rowid_ranges】个区域，每个区域都互不重叠，利用rowid做分界线。

同行情况下删除非分区表TB_ARTSIAN_ATTR上所有attr_id<99999999;的记录，如果不优化则是一条语句：

DELETE FROM TB_ARTSIAN_ATTRwhere attr_id<99999999;
COMMIT;

实际在很大的表上这样删除数据是不理想也不可行的，几点理由：

1. 单条SQL语句串行执行，速度缓慢

2. 运行时间过长可能引发ORA-1555等著名错误

3. 如果失败rollback回滚可能是一场灾难

若利用这里介绍的方法，则可以构造出多条DML语句并行删除，每一条均只删除一小部分：

DELETE FROM TB_ARTSIAN_ATTR where rowid between 'AAAYHtAAGAAAAEAAAA' and 'AAAYHtAALAAAx5yCcP' and  attr_id<99999999;
COMMIT;
DELETE FROM TB_ARTSIAN_ATTR where rowid between 'AAAYHtAALAAAx5zAAA' and 'AAAYHtAANAAA75yCcP' and  attr_id<99999999;
COMMIT;
.......
.......
.......

视你想要的并行度，将以上构成DML语句再分割几块，打开多个终端同时执行。

这样做的几个优点：

- 用户手动控制的并行执行，省去了Oracle Parallel并行控制的开销，使用得当的话比加parallel hint或者表上加并行度效率更高。

- 将数据分割成小块(chunks)来处理，避免了ORA-1555错误

- 用户可以根据主机负载和IO 动态地加减并行度

Step3: 外键校验以及通过存过清除分片数据

Step3.1: 外键校验

检查一下 TB_ARTSIAN_ATTR的外键和他表的外键管理，如果存在他表的外键关联该表的主键，他表上的外键要先去除。清理完记得恢复

disable / enable 也可以。

select 'ALTER TABLE ' || TABLE_NAME || ' drop CONSTRAINT ' ||
       constraint_name || '; ' as v_sql
  from user_constraints
where CONSTRAINT_TYPE in ('R' ) and 
   owner = 'ARTISAN'
   and upper(table_name) in ('TB_ARTSIAN_ATTR')
union all   
select 'ALTER TABLE ' || a.TABLE_NAME || ' drop CONSTRAINT ' ||
       a.constraint_name || '; ' as v_sql
  from user_constraints a ,user_constraints b
where 
   a.owner = 'ARTISAN'
   and a.owner=b.owner
   and a.r_owner=b.owner
   and upper(b.table_name) in ('TB_ARTSIAN_ATTR')
   and a.r_constraint_name=b.constraint_name;

Step3.2: 根据分片清除过期数据

CREATE OR REPLACE PROCEDURE PROC_CLEAN_BIGDATA IS
  V_SQL        VARCHAR2(4000);
  maxrows      NUMBER DEFAULT 30000;
  row_id_table DBMS_SQL.Urowid_Table;
  -- 过期数据
  CURSOR c1 IS
    select /*+ PARALLEL( s,3) */
     s.rowid row_id
      from TB_ARTSIAN_ATTR  s
           where rowid between 'AAAo2CAAHAADDwJAAA' and 'AAAo2CAAHAADEqICcP' and  --  上面分片 生成的 ROWID 范围
           ATTR_ID in ( select ATTR_ID from TB_ARTSIAN_INST_OLD where  INST_STATE ='C') ;
BEGIN
  --V_SQL := 'ALTER TABLE TB_ARTSIAN_ATTR nologging';
  --EXECUTE IMMEDIATE V_SQL;
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT
      INTO row_id_table LIMIT maxrows;
    EXIT WHEN row_id_table.COUNT = 0;
    FORALL i IN 1 .. row_id_table.COUNT
      DELETE FROM TB_ARTSIAN_ATTR WHERE ROWID = row_id_table(i);
      COMMIT;
       --dbms_lock.sleep(5000);
  END LOOP;
  CLOSE c1;
  --V_SQL := 'ALTER TABLE TB_ARTSIAN_ATTR logging';
  --EXECUTE IMMEDIATE V_SQL;
END PROC_CLEAN_BIGDATA;
/

Step3.3:FORALL和BULK COLLECT知识点

当PL/SQL运行时引擎处理一块代码时，它使用PL/SQL引擎来执行过程化的代码，而将SQL语句发送给SQL引擎来执行；SQL引擎执行完毕后，将结果再返回给PL/SQL引擎。这种在PL/SQL引擎和SQL引擎之间的交互，称为上下文交换（context switch）。每发生一次交换，就会带来一定的额外开销.

从Oracle 8i开始，PL/SQL得到了两点增强，可以将PL/SQL引擎和SQL引擎之间的多次上下文交换压缩为一次交换：

FORALL，用于增强PL/SQL引擎到SQL引擎的交换。

使用FORALL，可以将多个DML批量发送给SQL引擎来执行，最大限度地减少上下文交互所带来的开销

BULK COLLECT，用于增强SQL引擎到PL/SQL引擎的交换。

BULK COLLECT子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT

更多请参考 Oracle数据库之FORALL与BULK COLLECT语句

#Step4: 表分析，重新搜集统计信息

做一下表分析，给CBO提供更加精准的信息，使ORACLE选择更合理的执行计划

普通表：

---表分析下  （执行时间，取决于数据量）
BEGIN                                
 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => 'ARTISAN',
                                   TABNAME          => 'TB_ARTSIAN_ATTR',
                                   ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                   METHOD_OPT       => 'for all columns size repeat',
                                   DEGREE           => 8,
                                   CASCADE          => TRUE,
                                   no_invalidate    => false);
  END;

如果是分区表加上GRANULARITY => 'ALL',

GRANULARITY: 收集统计信息的粒度。(只应用于分区表),值包括:  
             'ALL':搜集(SUBPART
             TION,PARTITION,AND GLOBAL)统计信息。  
             'AUTO':基于分区的类型来决定粒度，默认值。  
             'DEFAULT':收集GLOBAL和PARTITION LEVEL的统计信息，等同与'GLOBAL AND PARTITION'。  
             'GLOBAL':收集全局统计信息  
             'GLOBAL AND PARTITION':收集GLOBAL和PARTITION LEVEL统计信息。  
             'GPARTITION':收集PARTITION-LEVEL的统计信息。  
             'SUBPARTITION':收集SUBPARTITION-LEVEL统计信息

#Step5: 优化

但是以上方法仍存在几点不足：

rowid_chunk.sql脚本目前不支持分区表
因为rowid_chunk.sql的脚本是根据表段的大小均匀地分割成指定数目的区域，试想当一些要更新或者删除的历史数据集中分布在segment的某些位置时(例如所要删除的数据均存放在一张表的前200个Extents中)，因为脚本是根据大小均匀分割区域的，所以某些区域是根本没有我们所要处理的数据的，由这些区域构造出来的DML语句都是无意义的.

基于以上这些考虑，重写了获取rowid分块的SQL脚本

select 'and rowid between ''' || ora_rowid || ''' and ''' ||
       lead(ora_rowid, 1) over(order by rn asc) || '''' || ';'
  from (
       with cnt as (select count(*) from TB_ARTSIAN_ATTR )             --   按需添加where 条件 （希望仅针对存有满足o条件数据的范围rowid分块）  注意替换这里！！
         select rn, ora_rowid
           from (select rownum rn, ora_rowid
                   from (select rowid ora_rowid
                           from TB_ARTSIAN_ATTR                   --  按需添加where 条件 （希望仅针对存有满足o条件数据的范围rowid分块）  注意替换这里！！
                          order by rowid))
          where rn in (select (rownum - 1) *
                              trunc((select * from cnt) / &row_range) + 1
                         from dba_tables
                        where rownum < &row_range                  --输入分区的数目
                       union
                       select * from cnt))

上述脚本同样可以实现rowid分区的目的，但是因为其rowid是直接取自SELECT语句查询，所以不存在不支持分区表等复杂对象的情况。也因为rowid是来源于SELECT，所以我们可以指定针对那些存在符合条件数据的范围分区。

几点注意事项：

请将该脚本放到Pl/SQL Developer或Toad之类的工具中运行，在sqlplus中运行可能出现ORA-00933
不要忘记替换标注中的条件
自行控制commit 避免出现ORA-1555错误

该脚本目前存在一个不足，在获取rowid分块时要求大表上有适当的索引，否则可能会因为全表扫描并排序而十分缓慢，若有恰当的索引则会使用INDEX FAST FULL SCAN。这里的恰当索引是指至少有一个非空列的普通b*tree索引，最好的情况是有主键索引或者bitmap位图索引。

Oracle在版本11.2中引入了DBMS_PARALLEL_EXECUTE 的新特性来帮助更新超大表

Oracle-使用切片删除的方式清理非分区表中的超巨数据

概述

Step1:rowid_chunk.sql

Step2:文件上传到Oracle主机用户，执行@rowid_chunk.sql

Step2.1: 上传脚本

Step2.2: 连接数据库，获取分片

Step3: 外键校验以及通过存过清除分片数据

Step3.1: 外键校验

Step3.2: 根据分片清除过期数据

Step3.3:FORALL和BULK COLLECT知识点

热门文章

最新文章

相关电子书

相关实验场景

推荐镜像