手把手教你大型表格的更新、刪除和優化
是否能學習分區表的方式，從邏輯上對單表進行分區，從而加快刪除的速度?說到此處，我們先來回顧下單表的物理存儲結構：段–區–塊。區是段的最小分配單元，一個區又包含多個塊，那么能否利用區或塊的物理特性來模擬分區呢?筆者嘗試使用區來做分區，為什么不用塊呢?因為一個數據庫塊能存儲的數據量不超過1000行，故被排除。
我們利用ROWID對每一行進行按區分片，此處引入了Oracle內部函數dbms_rowid.rowid_create幫助我們按區進行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;
按區分片后的信息輸出如下圖所示。
圖 按區分片后的信息輸出
有了以上的分片信息，我們只需要帶入需要篩選的條件，使用匿名塊批量刪除即可，具體實現方式如下：
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;
在具體的實現過程中，大家只需要替換對應的SQL語句及用戶名對象即可。
雖然按區構造ROWID分片進行刪除，效率上比單純的delete提高了好幾倍，但整個執行過程并不是并行的，需要在不同的窗口進行人工操作，實現過程較為煩瑣。那么還有沒有更高效的方式呢?
Oracle從11g R2版本開始推出了DBMS_PARALLEL_EXECUTE包，能夠高效地對大表進行DML操作。可以自定義并行度這一特點，使得DBMS_PARALLEL_EXECUTE包成為了最優的選擇。實現代碼如下：
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為行數，否則表示塊數
CHUNK_SIZE => 2500 <<<自定義chunk的大小，這里表示2500行為一個chunk
);
-- DML to be execute in parallel
l_sql_stmt := 'delete OB2 where object_type = ''SYNONYM'' and rowid BETWEEN
:start_id AND :end_id'; <<<想要執行的SQL語句
-- 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 <<<自定義執行并行度
);
-- 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包的使用方法較為簡單，只需要修改標紅的備注部分即可執行。以上這個腳本是通過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包涉及的相關視圖如下：
DBA_PARALLEL_EXECUTE_TASKS
DBA_PARALLEL_EXECUTE_CHUNKS
dba_scheduler_jobs
在任務的執行過程中，可以通過上述視圖實時監控任務的執行情況。
本文摘編于《DBA攻堅指南：左手Oracle，右手MySQL》，經出版方授權發布。(ISBN：9787111684336)轉載請保留文章出處。
