手把手教你大型表格的更新、删除和优化

导读：说到对历史数据的清理，分区表格较为简单，我们只需要截断（truncate）需要删除的历史分区即可，但对于一张普通的大表来说，清理历史数据就是个体力活了。传统方式用得比较多的是将delete与批量提交相结合，但速度往往不太理想。那么应该如何提高单表的delete效率呢？

是否能学习分区表的方式，从逻辑上对单表进行分区，从而加快删除的速度？说到此处，我们先来回顾下单表的物理存储结构：段–区–块。区是段的最小分配单元，一个区又包含多个块，那么能否利用区或块的物理特性来模拟分区呢？笔者尝试使用区来做分区，为什么不用块呢？因为一个数据库块能存储的数据量不超过1000行，故被排除。

我们利用ROWID对每一行进行按区分片，此处引入了Oracle内部函数dbms_rowid.rowid_create帮助我们按区进行ROWID分片，代码如下：

 1SQL> select A.FILE_ID,
 2
 3       A.EXTENT_ID,
 4
 5       A.BLOCK_ID,
 6
 7       A.BLOCKS,
 8
 9       ' rowid between ' || '''' ||
10
11       dbms_rowid.rowid_create(1,
12
13                               b.data_object_id,
14
15                               a.relative_fno,
16
17                               a.block_id,
18
19                               0) || '''' || ' and ' || '''' ||
20
21       dbms_rowid.rowid_create(1,
22
23                               b.data_object_id,
24
25                               a.relative_fno,
26
27                               a.block_id + blocks - 1,
28
29                               999) || ''';'
30
31  from dba_extents a, dba_objects b
32
33 where a.segment_name = b.object_name
34
35   and a.owner = b.owner
36
37   and b.object_name = 'JASON'
38
39   and b.owner = 'SCOTT'
40
41 order by a.relative_fno, a.block_id;

按区分片后的信息输出如下图所示。

图　按区分片后的信息输出

有了以上的分片信息，我们只需要带入需要筛选的条件，使用匿名块批量删除即可，具体实现方式如下：

 1SQL> declare
 2
 3  cursor cur_rowid is
 4
 5    select dbms_rowid.rowid_create(1,
 6
 7                                   b.data_object_id,
 8
 9                                   a.relative_fno,
10
11                                   a.block_id,
12
13                                   0) begin_rowid,
14
15           dbms_rowid.rowid_create(1,
16
17                                   b.data_object_id,
18
19                                   a.relative_fno,
20
21                                   a.block_id + blocks - 1,
22
23                                   999) end_rowid
24
25      from dba_extents a, dba_objects b
26
27     where a.segment_name = b.object_name
28
29       and a.owner = b.owner
30
31       and b.object_name = 'JASON'
32
33       and b.owner = 'SCOTT'
34
35     order by a.relative_fno, a.block_id;
36
37  r_sql varchar2(4000);
38
39begin
40
41  FOR cur in cur_rowid LOOP
42
43    r_sql := 'delete SCOTT.jason where OBJECT_TYPE=' || '''' || 'INDEX' || '''' ||
44
45             ' and rowid between :1 and :2';
46
47    EXECUTE IMMEDIATE r_sql
48
49      using cur.begin_rowid, cur.end_rowid;
50
51    COMMIT;
52
53  END LOOP;
54
55end;

在具体的实现过程中，大家只需要替换对应的SQL语句及用户名对象即可。

虽然按区构造ROWID分片进行删除，效率上比单纯的delete提高了好几倍，但整个执行过程并不是并行的，需要在不同的窗口进行人工操作，实现过程较为烦琐。那么还有没有更高效的方式呢？

Oracle从11g R2版本开始推出了DBMS_PARALLEL_EXECUTE包，能够高效地对大表进行DML操作。可以自定义并行度这一特点，使得DBMS_PARALLEL_EXECUTE包成为了最优的选择。实现代码如下：

  1SQL> SET SERVEROUTPUT ON
  2
  3SQL> BEGIN
  4
  5  DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
  6
  7EXCEPTION WHEN OTHERS THEN
  8
  9NULL;
 10
 11END;
 12
 13/
 14
 15
 16
 17SQL> DECLARE
 18
 19  l_task     VARCHAR2(30) := 'test_task';
 20
 21  l_sql_stmt VARCHAR2(32767);
 22
 23  l_try      NUMBER;
 24
 25  l_status   NUMBER;
 26
 27BEGIN
 28
 29  -- Create the TASK
 30
 31  DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
 32
 33  -- Chunk the table by the ROWID
 34
 35  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
 36
 37  (
 38
 39    TASK_NAME   => l_task,
 40
 41    TABLE_OWNER => 'JOE',    <<<用户名
 42
 43    TABLE_NAME  => 'OB2',    <<<表名
 44
 45    BY_ROW      => TRUE,     <<<值为TRUE，表示chunk_size为行数，否则表示块数
 46
 47    CHUNK_SIZE  => 2500      <<<自定义chunk的大小，这里表示2500行为一个chunk
 48
 49  );
 50
 51  -- DML to be execute in parallel
 52
 53  l_sql_stmt := 'delete OB2 where object_type = ''SYNONYM'' and rowid BETWEEN 
 54
 55      :start_id AND :end_id';    <<<想要执行的SQL语句
 56
 57  -- Run the task
 58
 59  DBMS_PARALLEL_EXECUTE.RUN_TASK
 60
 61  (
 62
 63    TASK_NAME      => l_task,
 64
 65    SQL_STMT       => l_sql_stmt,
 66
 67    LANGUAGE_FLAG  => DBMS_SQL.NATIVE,
 68
 69    PARALLEL_LEVEL => 2    <<<自定义执行并行度
 70
 71  );
 72
 73  -- If there is error, RESUME it for at most 2 times.
 74
 75  l_try := 0;
 76
 77  l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
 78
 79  WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
 80
 81  LOOP
 82
 83    l_try := l_try + 1;
 84
 85    DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task);
 86
 87    l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
 88
 89  END LOOP;
 90
 91  -- Done with processing; drop the task
 92
 93  DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task);
 94
 95EXCEPTION WHEN OTHERS THEN
 96
 97  DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM);
 98
 99END;
100
101/

如上述脚本所示，DBMS_PARALLEL_EXECUTE包的使用方法较为简单，只需要修改标红的备注部分即可执行。以上这个脚本是通过ROWID进行切割的，当然切割表的方法还有另外两种，一是通过指定字段CREATE_CHUNKS_BY_NUMBER_COL来切割，二是通过自己指定SQL语句CREATE_CHUNKS_BY_SQL来切割，这里就不详细说明了，大家如想进一步了解，可自行搜索相关资料。

DBMS_PARALLEL_EXECUTE的基本原理是将一个大表以指定的块大小（chunk size）进行分片（chunk size 可以指定行数或块数），然后对多个分片进行并行删除（delete）或其他DML操作，每一个分片完成后立即提交，最后通过调用job进行并发控制操作。

所以，如果想要调用DBMS_PARALLEL_EXECUTE包，除了拥有此包的访问权限之外，还必须要有创建job的权限。

DBMS_PARALLEL_EXECUTE包的基本执行流程具体如下。

1）调用create_task()，创建任务（task）。

2）调用create_chunk_by_rowid()，创建分块规则。

3）编写自己需要执行的DML操作语句。

4）调用run_task()，运行任务。

5）调用drop_task()，即任务执行完成后，删除任务。

DBMS_PARALLEL_EXECUTE包涉及的相关视图如下：

1DBA_PARALLEL_EXECUTE_TASKS
2
3DBA_PARALLEL_EXECUTE_CHUNKS
4
5dba_scheduler_jobs

在任务的执行过程中，可以通过上述视图实时监控任务的执行情况。

【END】

本文摘编于《DBA攻坚指南：左手Oracle，右手MySQL》，经出版方授权发布

推荐语：数据库领域资深数据库专家的经验总结和最佳实践，由浅入深地解析典型案例的运维方法论。本书是美创科技资深Oracle、MySQL技术专家呕心沥血之作，积作者多年的经验结晶和实践经验，也是目前市场上为数不多Oracle和MySQL相结合的数据库技术书籍。