大型表格的更新.docx

《大型表格的更新.docx》由会员分享，可在线阅读，更多相关《大型表格的更新.docx（12页珍藏版）》请在第一文库网上搜索。

1、大型表格的更新、删除和优化导读：说到对历史数据的清理，分区表格较为简单，我们只需要截断(truncate)需要删除的历史分区即可，但对于一张普通的大表来说，清理历史数据就是个体力活了。传统方式用得比较多的是将delete与批量提交相结合，但速度往往不太理想。那么应该如何提高单表的delete效率呢？是否能学习分区表的方式，从逻辑上对单表进行分区，从而加快删除的速度？说到此处，我们先来回顾下单表的物理存储结构：段-区-块。区是段的最小分配单元，一个区又包含多个块，那么能否利用区或块的物理特性来模拟分区呢？笔者尝试使用区来做分区，为什么不用块呢？因为一个数据库块能存储的数据量不超过1000行，故被

2、排除。我们利用R0WTD对每一行进行按区分片，此处引入了 Oracle内部函数dbms rowid. rowid create帮助我们按区进行ROW ID分片，代码如下：1SQL select A. FILE ID,3A.EXTENTD,M5A.BLOCKD,B7A. BLOCKS,9 rowid betweendbms_rowid. rowid_create(l,13b. data_object_i15a. relative_fno,dbms_rowid rowid_create(1,3b.data_object_i5a.relative_fno,27a. block_id +33 wher

3、e a. segment_nameb. object_name35 and a. owner = b. owner1 and b- object_name = JASON389 and b. owner = SCOTT10order by a. relative_fno, a. block_id;按区分片后的信息输出如下图所示。FILE ID J EXTENT ID I BLOCK ID ! BLOCKS l? ROWIDBETWEElT 11 DBMS ROY179688 rovid between ? AAAWT7AAEAAAEYwAAA/ n4|0,T421797617984179921

4、8IJIJ0180081801618024180321804018304183121832018328183361834418432185601868818816888888888819072192001932819456rovid betvrovid btvrowid btwrovid be tvrovid bttvrovid bttwrovid betwrowid betwrovid btwrovid betvrowid bttvrowid bttwrovid betvrowid betwrowid bttwrowid betwrowid betwrowid betwrowid btwro

5、wid betwrowid betwrowid btvrowid betw8 8 8 8 8n AAAVT7AAEAAAEY4AAA ann AAAYT7AAEAAAEZAAAA/ ann MMTT7MEMAEZIAJ* ann AAAVT7AAEAAAEZQAJUC ann MAVT7AAEMAEZYMA，ann AAMT 7 AAE AAAEZ gAU tnn AAAYT7AAEMAEZOAM mn AAAVT 7 AKE AAAEZ vann AAATT7AAEMAEZ4AAA ann AAAYT 7 AAE AAAE MU/ enn AAMrT7AAEUAEIAAA，ann MAEAA

6、EUAEeQAAA，ann MMTT7AAEAME4AAA ann WmMEMAE.WC an七 MAVT7AAEUKE.oAMCn MAYT7MEAAAE川WC ann AAMTT7AAEMAEiAAAA ann AAAVTrAAEAAAEkAAAA/2M七 AAMrT7AAEAAAEAAAA，n AAMTT7AAEUAEoMAA tnn ? AAAWT7MEAAAEqAAAA? 5n AAAYT7AAEAAAESAUA/ 必n MAYT7AAzAAAEuUAA，ann，AAAWT7ME入AAE#AAAA， an图按区分片后的信息输出有了以上的分片信息，我们只需要带入需要筛选的条件，使用匿名

7、块批量删除即可,具体实现方式如下：1SQL declaiJa. relati|11a. block一IE130) begin_rowid, IEE15dbms_rowid. rowid_create (1,17b. data)bject_id,19a. relative_fno,25from dba_extents a, dba_objects b7where a. segment_name = b. object_name|29and a. owner = b. owner.pland b.object_name = JASON3and b.owner = SCOTT|35order by

8、a. relative_fno, a. block_id;3 r_sql := delete SCOTT, jason where OBJECT TYPE=EKI45 and row id between : 1 and :2;3 I ND LOOP;55cnd;在具体的实现过程中，大家只需要替换对应的SQL语句及用户名对象即可。虽然按区构造ROWID分片进行删除，效率上比单纯的delete提高了好几倍，但整个执行过程并不是并行的，需要在不同的窗口进行人工操作，实现过程较为烦琐。那么还有没有更高效的方式呢？Oracle从11g R2版本开始推出了 DBMS_PARALLEL_EXECUTE包，

9、能够高效地对大表进行DML操作。可以自定义并行度这一特点，使得DBMS_PARALLEL_EXECUTE包成为了最优的选择。实现代码如下：1SQL SET SERVEROUTPUT 03SQL5 DBMS PARALLEL EXECUTE. DROP TASK C test task);I、, r11END;IKE1317SQL19 l_taskVARCHAR2(30) := test task;I23 l_tryNUMBER;29Create the TASK3031 DBMS_PARALLEL_EXECUTE.CREATE_TASK (taskjame = :Ltask)一 topara

10、lieid BETWEENw16PARALlnVEa3ufSfH71);73- H there is error, Rm it for at most 2 times.77 I stilus :二 l)lilS IWIMI.I.IJ. I,.XIXI Th：. TASK STATIS(1 bisk)79 WHILE(l_try 2 and l_status != DBMS_PARALLEL_EXECUTE. FINISHED)85 DBMS_PARALLEL_EXECUTE. RESUME_TASK(l_task);8687 Lstatus :二 DBMS_PARALLEL_EXECUTE.

11、TASK_STATUS (l_task);8889l：l) LOOP;9091- Done with processing; drop the task93 DBMS PARALLEL EXECUTE. DROP TASK(1 task);95EXCEPTION WHEN OTHERS9697 DBMS.OUTPUT. PUT_LINE( Error in the code : SQLERRM);98101如上述脚本所示，DBMS_PARALLEL_EXECUTE包的使用方法较为简单，只需要修改标红的备注部分即可执行。以上这个脚本是通过ROWID进行切割的，当然切割表的方法还有另外两种，一是通

12、过指定字段CREATE_CHUNKS_BY_NUMBER_COL来切割，二是通过自己指定SQL语句CREATE_CHUNKS_BY_SQL来切割，这里就不详细说明了，大家如想进一步了解，可自行搜索相关资料。DBMS_PARALLEL_EXECUTE的基本原理是将一个大表以指定的块大小(chunk size)进行分片(chunk size可以指定行数或块数)，然后对多个分片进行并行删除(delete)或其他DML操作，每一个分片完成后立即提交，最后通过调用job进行并发控制操作。所以，如果想要调用DBMS_PARALLEL_EXECUTE包，除了拥有此包的访问权限之外,还必须要有创建job的权限

13、。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 3DBA PARALLEL EXECUTE CHUNKS 5dba_scheduler_jobs在任务的执行过程中，可以通过上述视图实时监控任务的执行情况。