手把手教你大型表格的更新、刪除和優(yōu)化
是否能學(xué)習(xí)分區(qū)表的方式，從邏輯上對(duì)單表進(jìn)行分區(qū)，從而加快刪除的速度?說(shuō)到此處，我們先來(lái)回顧下單表的物理存儲(chǔ)結(jié)構(gòu)：段–區(qū)–塊。區(qū)是段的最小分配單元，一個(gè)區(qū)又包含多個(gè)塊，那么能否利用區(qū)或塊的物理特性來(lái)模擬分區(qū)呢?筆者嘗試使用區(qū)來(lái)做分區(qū)，為什么不用塊呢?因?yàn)橐粋€(gè)數(shù)據(jù)庫(kù)塊能存儲(chǔ)的數(shù)據(jù)量不超過(guò)1000行，故被排除。
我們利用ROWID對(duì)每一行進(jìn)行按區(qū)分片，此處引入了Oracle內(nèi)部函數(shù)dbms_rowid.rowid_create幫助我們按區(qū)進(jìn)行ROWID分片，代碼如下：
SQL> select A.FILE_ID,
A.EXTENT_ID,
A.BLOCK_ID,
A.BLOCKS,
' rowid between ' || '''' ||
dbms_rowid.rowid_create(1,
b.data_object_id,
a.relative_fno,
a.block_id,
0) || '''' || ' and ' || '''' ||
dbms_rowid.rowid_create(1,
b.data_object_id,
a.relative_fno,
a.block_id + blocks - 1,
999) || ''';'
from dba_extents a, dba_objects b
where a.segment_name = b.object_name
and a.owner = b.owner
and b.object_name = 'JASON'
and b.owner = 'SCOTT'
order by a.relative_fno, a.block_id;
按區(qū)分片后的信息輸出如下圖所示。
圖 按區(qū)分片后的信息輸出
有了以上的分片信息，我們只需要帶入需要篩選的條件，使用匿名塊批量刪除即可，具體實(shí)現(xiàn)方式如下：
SQL> declare
cursor cur_rowid is
select dbms_rowid.rowid_create(1,
b.data_object_id,
a.relative_fno,
a.block_id,
0) begin_rowid,
dbms_rowid.rowid_create(1,
b.data_object_id,
a.relative_fno,
a.block_id + blocks - 1,
999) end_rowid
from dba_extents a, dba_objects b
where a.segment_name = b.object_name
and a.owner = b.owner
and b.object_name = 'JASON'
and b.owner = 'SCOTT'
order by a.relative_fno, a.block_id;
r_sql varchar2(4000);
begin
FOR cur in cur_rowid LOOP
r_sql := 'delete SCOTT.jason where OBJECT_TYPE=' || '''' || 'INDEX' || '''' ||
' and rowid between :1 and :2';
EXECUTE IMMEDIATE r_sql
using cur.begin_rowid, cur.end_rowid;
COMMIT;
END LOOP;
end;
在具體的實(shí)現(xiàn)過(guò)程中，大家只需要替換對(duì)應(yīng)的SQL語(yǔ)句及用戶名對(duì)象即可。
雖然按區(qū)構(gòu)造ROWID分片進(jìn)行刪除，效率上比單純的delete提高了好幾倍，但整個(gè)執(zhí)行過(guò)程并不是并行的，需要在不同的窗口進(jìn)行人工操作，實(shí)現(xiàn)過(guò)程較為煩瑣。那么還有沒(méi)有更高效的方式呢?
Oracle從11g R2版本開(kāi)始推出了DBMS_PARALLEL_EXECUTE包，能夠高效地對(duì)大表進(jìn)行DML操作?？梢宰远x并行度這一特點(diǎn)，使得DBMS_PARALLEL_EXECUTE包成為了最優(yōu)的選擇。實(shí)現(xiàn)代碼如下：
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/
SQL> DECLARE
l_task VARCHAR2(30) := 'test_task';
l_sql_stmt VARCHAR2(32767);
l_try NUMBER;
l_status NUMBER;
BEGIN
-- Create the TASK
DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
-- Chunk the table by the ROWID
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
(
TASK_NAME => l_task,
TABLE_OWNER => 'JOE', <<<用戶名
TABLE_NAME => 'OB2', <<<表名
BY_ROW => TRUE, <<<值為TRUE，表示chunk_size為行數(shù)，否則表示塊數(shù)
CHUNK_SIZE => 2500 <<<自定義chunk的大小，這里表示2500行為一個(gè)chunk
);
-- DML to be execute in parallel
l_sql_stmt := 'delete OB2 where object_type = ''SYNONYM'' and rowid BETWEEN
:start_id AND :end_id'; <<<想要執(zhí)行的SQL語(yǔ)句
-- Run the task
DBMS_PARALLEL_EXECUTE.RUN_TASK
(
TASK_NAME => l_task,
SQL_STMT => l_sql_stmt,
LANGUAGE_FLAG => DBMS_SQL.NATIVE,
PARALLEL_LEVEL => 2 <<<自定義執(zhí)行并行度
);
-- If there is error, RESUME it for at most 2 times.
l_try := 0;
l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
l_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task);
l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
END LOOP;
-- Done with processing; drop the task
DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM);
END;
/
如上述腳本所示，DBMS_PARALLEL_EXECUTE包的使用方法較為簡(jiǎn)單，只需要修改標(biāo)紅的備注部分即可執(zhí)行。以上這個(gè)腳本是通過(guò)ROWID進(jìn)行切割的，當(dāng)然切割表的方法還有另外兩種，一是通過(guò)指定字段CREATE_CHUNKS_BY_NUMBER_COL來(lái)切割，二是通過(guò)自己指定SQL語(yǔ)句CREATE_CHUNKS_BY_SQL來(lái)切割，這里就不詳細(xì)說(shuō)明了，大家如想進(jìn)一步了解，可自行搜索相關(guān)資料。
DBMS_PARALLEL_EXECUTE的基本原理是將一個(gè)大表以指定的塊大小(chunk size)進(jìn)行分片(chunk size 可以指定行數(shù)或塊數(shù))，然后對(duì)多個(gè)分片進(jìn)行并行刪除(delete)或其他DML操作，每一個(gè)分片完成后立即提交，最后通過(guò)調(diào)用job進(jìn)行并發(fā)控制操作。
所以，如果想要調(diào)用DBMS_PARALLEL_EXECUTE包，除了擁有此包的訪問(wèn)權(quán)限之外，還必須要有創(chuàng)建job的權(quán)限。
DBMS_PARALLEL_EXECUTE包的基本執(zhí)行流程具體如下。
1)調(diào)用create_task()，創(chuàng)建任務(wù)(task)。
2)調(diào)用create_chunk_by_rowid()，創(chuàng)建分塊規(guī)則。
3)編寫自己需要執(zhí)行的DML操作語(yǔ)句。
4)調(diào)用run_task()，運(yùn)行任務(wù)。
5)調(diào)用drop_task()，即任務(wù)執(zhí)行完成后，刪除任務(wù)。
DBMS_PARALLEL_EXECUTE包涉及的相關(guān)視圖如下：
DBA_PARALLEL_EXECUTE_TASKS
DBA_PARALLEL_EXECUTE_CHUNKS
dba_scheduler_jobs
在任務(wù)的執(zhí)行過(guò)程中，可以通過(guò)上述視圖實(shí)時(shí)監(jiān)控任務(wù)的執(zhí)行情況。
本文摘編于《DBA攻堅(jiān)指南：左手Oracle，右手MySQL》，經(jīng)出版方授權(quán)發(fā)布。(ISBN：9787111684336)轉(zhuǎn)載請(qǐng)保留文章出處。
