说到对历史数据的清理,分区表格较为简单,我们只需要截断(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
在任务的执行过程中,可以通过上述视图实时监控任务的执行情况。
本文摘编于《DBA攻坚指南:左手Oracle,右手MySQL》,经出版方授权发布。(ISBN:9787111684336)转载请保留文章出处。
推荐语:数据库领域资深数据库专家的经验总结和最佳实践,由浅入深地解析典型案例的运维方法论。本书是美创科技资深Oracle、MySQL技术专家呕心沥血之作,积作者多年的经验结晶和实践经验,也是目前市场上为数不多Oracle和MySQL相结合的数据库技术书籍。