SQL語句是一種方便的語言，同樣也是一種“迷惑性”的語言。這個主要體現在它的集合操作特性上。無論資料表資料量是1條，還是1億條，更新的語句都是完全相同。但是，實際執行結果（或者能否出現結果）卻是有很大的差異。

筆者在開發DBA領域的一個理念是：作為開發人員，對資料庫、對資料要有敬畏之心，一個語句發出之前，起碼要考慮兩個問題：目標資料表的總資料量是多少（投產之後）？你這個操作會涉及到多大的資料量？不同的回答，處理的方案其實是不同的。

更新大表資料，是我們在開發和運維，特別是在資料遷移領域經常遇到的一種場景。上面兩個問題的回答是：目標資料表整體就很大，而且更新範圍也很大。一個SQL從理論上可以處理。但是在實際中，這種方案會有很多問題。

本篇主要介紹幾種常見的大表處理策略，並且分析出他們的優劣。作為我們開發人員和DBA，選取的標準也是靈活的：根據你的操作型別（運維操作還是系統日常作業）、程式執行環境（硬體環境是否支援並行）和程式設計環境（是否可以完全獨佔所有資源）來綜合考量決定。

首先，我們需要準備出一張大表。

1、環境準備

我們選擇Oracle 11.2版本進行試驗。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

準備一張大表。

SQL> create table t as select * from dba_objects;

Table created

SQL> insert into t select * from t;

72797 rows inserted

SQL> insert into t select * from t;

145594 rows inserted

（篇幅原因，中間過程略……）

SQL> commit;

Commit complete

SQL> select bytes/1024/1024/1024 from dba_segments where owner='SYS' and segment_name='T';

BYTES/1024/1024/1024

--------------------

1.0673828125

SQL> select count(*) from t;

COUNT(*)

----------

9318016

Executed in 14.711 seconds

資料表T作為資料來源，一共包括9百多萬條記錄，合計空間1G左右。筆者實驗環境是在虛擬機器上，一顆虛擬CPU，所以後面進行並行Parallel操作的方案就是示意性質，不具有代表性。

下面我們來看最簡單的一種方法，直接update。

2、方法1：直接Update

最簡單，也是最容易出問題的方法，就是“不管三七二十一”，直接update資料表。即使很多老程式設計師和DBA，也總是選擇出這樣的策略方法。其實，即使結果能出來，也有很大的僥倖成分在其中。

我們首先看筆者的實驗，之後討論其中的原因。先建立一張實驗資料表t_target。

SQL> create table t_targettablespace users as select * from t;

Table created

SQL> update t_target set owner=to_char(length(owner));

（長時間等待……）

在等待期間，筆者發現如下幾個現象：

ü 資料庫伺服器執行速度奇慢，很多連線操作速度減緩，一段時間甚至無法登陸；

ü 後臺會話等待時間集中在資料讀取、log space buffer、空間分配等事件上；

ü 長期等待，作業系統層面開始出現異常。Undo表空間膨脹；

ü 日誌切換頻繁；

此外，選擇這樣策略的朋友還可能遇到：前臺錯誤丟擲異常、客戶端連線被斷開等等現象。

筆者遇到這樣的場景也是比較糾結，首先，長時間等待（甚至一夜）可能最終沒有任何結果。最要命的是也不敢輕易的撤銷操作，因為Oracle要進行update操作的回滾動作。一個小時之後，筆者放棄。

updatet_target set owner=to_char(length(owner))

ORA-01013: 使用者請求取消當前的操作

（接近一小時未完成）

之後就是相同時間的rollback等待，通常是事務執行過多長時間，回滾進行多長時間。期間，可以透過x$ktuxe後臺內部表來觀察、測算回滾速度。這個過程中，我們只有“乖乖等待”。

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA<>'INACTIVE';

KTUXESIZ

----------

62877

（……）

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA<>'INACTIVE';

KTUXESIZ

----------

511

綜合這種策略的結果通常是：同業抱怨（影響了他們的作業執行）、提心吊膽（不知道執行到哪裡了）、資源耗盡（CPU、記憶體或者IO佔到滿）、勞而無功（最後還是被rollback）。如果是正式投產環境，還要承擔影響業務生產的責任。

我們詳細分析一下這種策略的問題：

首先，我們需要承認這種方式的優點，就是簡單和片面的高效。相對於在本文中其他介紹的方法，這種方式程式碼量是最少的。而且，這種方法一次性的將所有的任務提交給資料庫SQL引擎，可以最大程度的發揮系統一個方面（CPU、IO或者記憶體）的能力。

如果我們的資料表比較小，經驗值在幾萬一下，這種方法是比較合適的。我們可以考慮使用。

另一方面，我們要看到Oracle Update的另一個方面，就是Undo、Redo和程式工作負載的問題。熟悉Oracle的朋友們知道，在DML操作的時候，Undo和Redo是非常重要的方面。當我們在Update和Delete資料的時候，資料塊被修改之前的“前映象”就會儲存在Undo Tablespace裡面。注意：Undo Tablespace是一種特殊的表空間，需要儲存在磁碟上。Undo的存在主要是為了支援資料庫其他會話的“一致讀”操作。只要事務沒有被commit或者rollback，Undo資料就會一直保留在資料庫中，而且不能被“覆蓋”。

Redo記錄了進行DML操作的“後映象”，Redo生成是和我們修改的資料量相關。現實問題要修改多少條記錄，生成的Redo總量是不變的，除非我們嘗試nologging選項。Redo單個日誌成員如果比較小，Oracle應用生成Redo速度比較大。Redo Group切換頻度高，系統中就面臨著大量的日誌切換或者Log Space Buffer相關的等待事件。

如果我們選擇第一種方法，Undo表空間就是一個很大的瓶頸。大量的前映象資料儲存在Undo表空間中不能釋放，繼而不斷的引起Undo檔案膨脹。如果Undo檔案不允許膨脹（autoextend=no），Oracle DML操作會在一定時候報錯。即使允許進行膨脹，也會伴隨大量的資料檔案DBWR寫入動作。這也就是我們在進行大量update的時候，在event等待事件中能看到很多的DBWR寫入。因為，這些寫入中，不一定都是更新你的資料表，裡面很多都是Undo表空間寫入。

同時，長時間的等待操作，觸動Oracle和OS的負載上限，很多奇怪的事情也可能出現。比如程式僵死、連線被斷開。

這種方式最大的問題在於rollback動作。如果我們在長時間的事務過程中，發生一些異常報錯，通常是由於資料異常，整個資料需要回滾。回滾是Oracle自我保護，維持事務完整性的工具。當一個長期DML update動作發生，中斷的時候，Oracle就會進入自我的rollback階段，直至最後完成。這個過程中，系統是比較執行緩慢的。即使重啟伺服器，rollback過程也會完成。

所以，這種方法在處理大表的時候，一定要慎用！！起碼要評估一下風險。

3、方法2：PL/SQL匿名塊

上面方法1的最大問題在於“一次性瞬間壓力”大。無論是Undo量、還是Rollback量，都是有很大的問題。即使我們的系統能夠支援這樣的操作，如果update過程中存在其他的作業，必然受到影響。

PL/SQL匿名塊的原則在於平穩負載，分批的進行處理。這個過程需要使用bulk collect批次操作，進行遊標操作。

我們首先還原實驗環境。

SQL> truncate table t_target;

Table truncated

SQL> insert /*+append*/into t_target select * from t;

9318016 rows inserted

SQL> commit;

Commit complete

程式碼片段。

SQL> set timing on;

SQL> declare

2 type rowid_list is table of urowid index by binary_integer;

3 rowid_infosrowid_list;

4 i number;

5 cursor c_rowids is select rowid from t_target;

6 begin

7 open c_rowids;

8

9 loop

10 fetch c_rowidsbulk collect into rowid_infos limit 2000;

11

12 foralli in 1..rowid_infos.count

13 update t_target set owner=to_char(length(owner)+1)

14 where rowid=rowid_infos(i);

15

16 commit;

17 exit when rowid_infos.count<2000;

18 end loop;

19 close c_rowids;

20 end;

21 /

PL/SQL procedure successfully completed

Executed in 977.081 seconds

我們在977s完成了整個操作。這個方法有幾個特點：首先是批次的獲取bulk collect指定數量更新批次。第二個是使用forall的方法批次更新，減少引擎之間的切換。第三是更新一批之後，commit操作。

這樣的好處在於平穩化undo使用。如果資料量少，這種程式碼方法可能比直接update要慢。但是如果資料量大，特別是海量資料情況下，這種方法是可以支援非常大的資料表更新的。

程式碼中update操作，使用rowid，如果有其他業務方面的主鍵也可以使用替換。

在程式設計實踐中，有時候我們可能不能使用PL/SQL程式碼片段，只能使用SQL語句。這種時候就需要結合業務方面有沒有特點可以使用？這種時候往往也就考驗開發人員對業務特點的理解了。

在使用forall的時候，要注意一批更新的數量。根據一些Oracle文獻透露，內部SQL引擎在update的時候，也是分批進行的，每批大約1000條記錄。經驗告訴我們每批數量在1-5萬比較合適。

這是第二種方法，下面我們介紹一種簡單、可行的手段，比較方便。

4、方法3：insert append方法

從Undo和Redo的角度看，我們更喜歡insert，特別是生成較少redo的nologging和append插入。Update和Delete操作，都會生成Undo記錄，在我們看來，都是可以想辦法減少的方法。

這種方法的思路是：利用insert，將原來的資料表插入到一個新建立的資料表。在insert過程中，整理column的取值，起到update相同的效果。

下面是實驗過程。我們先建立資料表，注意可以設定nologging屬性。

SQL> create tablet_renamenologging as select * from t_target where 1=0;

Table created

Executed in 0.889 seconds

在這個過程中，我們建立的是一個空表。之後就可以插入資料，這種方法比較麻煩的地方，就是需要在insert指令碼中列出所有的資料列。當然，借用一些工具技巧，這個過程也可以很簡單。

SQL> insert /*+append*/into t_rename

2 selectto_char(length(owner)) owner,

3 OBJECT_NAME,

4 SUBOBJECT_NAME,

5 OBJECT_ID,

6 DATA_OBJECT_ID,

7 OBJECT_TYPE,

8 CREATED,

9 LAST_DDL_TIME,

10 TIMESTAMP,

11 STATUS,

12 TEMPORARY,

13 GENERATED,

14 SECONDARY,

15 NAMESPACE,

16 EDITION_NAME from t_target;

9318016 rows inserted

Executed in 300.333 seconds

使用append操作，可以減少redo log的生成。從結果看，一共執行了300s左右，效率應該是比較好的。

之後，提交事務。將原來的資料表刪除，將新資料表rename成原有資料表名稱。

SQL> commit;

Commit complete

Executed in 0.031 seconds

SQL> drop table t_target purge;

Table dropped

Executed in 1.467 seconds

SQL> rename t_rename to t_target;

Table renamed

Executed in 0.499 seconds

SQL> select count(*) from t_target;

COUNT(*)

----------

9318016

Executed in 14.336 seconds

最後，可以將nologging屬性修改回來，將資料表約束新增上。

SQL> alter table t_target logging;

Table altered

Executed in 0.015 seconds

這種方法的好處在於效率，在資料量維持中高的情況下，這種方法速度是比較吸引人的。但是，這種方式也要消耗更多的儲存空間。在儲存空間允許的情況下，可以用這種方法。

如果資料量更大，達到海量的程度，比如幾十G的資料表，這種方法就值得考量一下了。需要結合硬體環境和執行環境完成。另外，這種方法涉及到資料表的建立等運維工作特性，故不適合在應用程式中使用，適合在運維人員過程中使用。

還有，就是這種方法的實際對備份的影響。由於我們使用了nologging+append選項，生成的redo log數量是不足以進行還原的，所以如果要實現完全恢復的話，資料庫實際上是失去了連續還原的依據。因此，如果真正使用了這個方法在生產環境下，之後需要進行資料庫全備份操作。

如果資料庫版本為11.2以上，我們可以使用Oracle的一個新特性dbms_parallel_execute包，進行資料表並行更新。詳見下面介紹。

5、方法3：dbms_paralle_execute並行包使用

其他最佳化手段都用上的時候，並行是可以嘗試的方法。並行parallel就是利用多個process同時進行處理，從而提高處理效率的方法。Parallel的使用有一些前提，也有一些不良反應。並行的前提是硬體支援，並行技術本身要消耗很多的資源，相當於是將伺服器資源“榨乾”來提速。在規劃並行策略的時候，首先要看硬體資源是不是支援，單核CPU情況下，也就不需要使用這個技術了。

使用並行之後，必然對其他正在執行程式、作業有影響。所以，筆者的經驗是：一般應用不要考慮並行的事情，如果發現特定場景存在並行的需要，可以聯絡DBA或者運維人員確定可控的技術方案。

在11.2之前，使用並行稍微複雜一些，很多朋友在使用的時候經常是“有名無實”，看似設定了並行，但是實際還是單程式執行。11.2之後，Oracle提供了新的並行操作介面dbms_parallel_execute，這讓並行更加簡單。

說明：本篇不是專門介紹dbms_parallel_execute介面，只作為介紹。詳細內容參見筆者專門介紹這個介面的文章。

dbms_parallel_execute工作採用作業task方式，後臺執行。首先是按照特定的原則進行資料分割，將工作資料集合分割為若干chunk。之後啟動多個後臺job進行工作。在劃分工作集合的問題上，Oracle提供了三種方法，rowid、column_value和SQL，分別按照rowid、列值和特定SQL語句進行分割。

注意：使用dbms_parallel_execute介面包有一個前提，就是job_queue_process引數必須設定非空。如果為0，則我們的程式執行之後被阻塞掛起。

恢復資料環境。

SQL> create table t_targettablespace users as select * from t where 1=0;

Table created

Executed in 0.078 seconds

SQL> insert /*+append*/into t_target select * from t;

9318016 rows inserted

Executed in 64.974 seconds

SQL> commit;

Commit complete

Executed in 0.109 seconds

引數環境。

SQL> show parameter job_queue

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes integer 1000

在這個任務中，我們選擇使用create_chunks_by_rowid方法。這種方法通用型強，執行計劃穩定性好。

SQL> set serveroutput on;

SQL> declare

2 vc_sqlvarchar2(1000);

3 n_try number;

4 n_status number;

5 begin

6 --create a task

7 dbms_parallel_execute.create_task(task_name => 'Huge_Update');

8

9 --By Rowid

10 dbms_parallel_execute.create_chunks_by_rowid(task_name => 'Huge_Update',

11 table_owner => 'SYS',table_name => 'T_TARGET',by_row =>true,chunk_size => 10000);

12

13 vc_sql := 'update /*+rowid(dda)*/t_target set owner=to_char(length(owner)) where rowid between :start_id and :end_id';

14

15 dbms_parallel_execute.run_task(task_name => 'Huge_Update',sql_stmt =>vc_sql,language_flag =>dbms_sql.native,parallel_level => 3);

16 --防止失敗後重啟

17 n_try := 0;

18 n_status := dbms_parallel_execute.task_status('Huge_Update');

19 while (n_try<2 and (n_status != dbms_parallel_execute.FINISHED)) loop

20 n_try := n_try + 1;

21 dbms_parallel_execute.resume_task('Huge_Update');

22 n_status := dbms_parallel_execute.task_status('Huge_Update');

23 end loop;

24

25 dbms_output.put_line(''||n_try);

26 dbms_parallel_execute.drop_task('Huge_Update');

27 end;

28 /

0

PL/SQL procedure successfully completed

Executed in 1177.106 seconds

在程式碼中，需要注意start_id和end_id兩個繫結變數。這兩個範圍值是介面固定的。這種方法使用了1177s來完成工作。

在執行過程中，我們也有很多方法來監督執行過程。Oracle提供了兩個檢視，關於parallel_execute介面的。Dba_parallel_execute_tasks表示了提交的任務，裡面我們可以看到狀態資訊。

SQL> col task_name for a15;

SQL> select task_name, status from dba_parallel_execute_tasks;

TASK_NAME STATUS

------------------- -------------------

Huge_Update PROCESSING

SQL> select task_name, JOB_PREFIX from dba_parallel_execute_tasks;

TASK_NAME JOB_PREFIX

-------------------- ------------------------------

Huge_Update TASK$_655

執行中，我們從v$session中可能看到後臺的程式會話。

SQL> select sid, serial#, status, PROGRAM, SQL_ID, event from v$session where action like 'TASK$_655%';

SID SERIAL# STATUS PROGRAM SQL_ID EVENT

---------- ---------- -------- ------------------------------------------------ ------------- ----------------------------------------------------------------

35 507 ACTIVE oracle@bspdev.localdomain (J003) d7xw8z3nzh5cg db file scattered read

38 119 ACTIVE oracle@bspdev.localdomain (J001) d7xw8z3nzh5cg log buffer space

45 6612 ACTIVE oracle@bspdev.localdomain (J000) d7xw8z3nzh5cg Data file init write

另一個檢視更有用dba_parallel_execute_chunks，其中包括了所有的chunk物件。Parallel Execute執行的原則就是資料的劃分，這個檢視中，可以看到哪些chunk已經完成，哪些沒有完成。

SQL> select status, count(*) from dba_parallel_execute_chunks group by status;

STATUS COUNT(*)

-------------------- ----------

ASSIGNED 3

UNASSIGNED 2315

PROCESSED 571

ASSIGNED狀態表示多程式狀態正在處理，我們設定了三個後臺程式執行。UNASSIGNED表示沒有完成，正等待處理。PROCESSED表示已經處理完。

這種方法應該是目前找到比較好的方法。缺點就是程式碼量比較大。優點是處理使用並行，如果物理條件支援，執行效率是很高的。而且，在海量資料表的情況下，這種策略是很值得推薦的。

6、結論

更新大量的資料，是我們常見的一種問題場景。無論對開發人員，還是運維人員，都有不同的挑戰。筆者本篇要強調的是：沒有絕對好的策略，都是針對特別的場景和背景，選取最適合的策略。從而更好地完成任務。盲目的執行SQL語句，是一種典型不負責任的行為，需要避免杜絕。

Oracle中如何更新一張大表記錄

相關文章