利用rowid快速在線更新海量數據
最近一直在折騰大表的更新問題，今天終于有了突破。興奮之余發個帖子跟大家分享一下心得，并且討論一下是否還可能進一步提高處理速度。
問題是這樣的：一張5億條記錄的表，沒有分區。由于增加了一個冗余字段，需要根據另外一張表（4.8億條）更新這個大表。下面是具體的描述：
環境：HP-UX 11i+Oracle9.2.0.8+RAID
要更新的表：T1 (id1 number, id2 number, curr_count number,.....)   --id1唯一  5億條記錄 >60GB
更新數據來源：T2 (id2 number, curr_count number)   --id2唯一  4.8億
更新邏輯：T2中的每一條記錄，都到T1中找到對應的記錄（T2.id2=T1.id2），更新T1.curr_count=T2.curr_count
限制條件：只能在線更新（應用程序一直在訪問這個表，所以不能用INSERT SELECT），不能占用太多系統資源，要求3天之內更新完畢。
原來的做法：
declare
  cursor cur_t2 is
    select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
       T2.id2, T2.curr_count, T1.rowid row_id
    from T1, T2
    where T1.id2=T2.id2;
  v_counter number;
begin
  v_counter := 0;
  for row_t2 in cur_t2 loop
    update T1 set curr_count=row_t2.curr_count
       where rowid=row_t2.row_id;
    v_counter := v_counter + 1;
    if (v_counter>=1000) then
      commit;
      v_counter := 0;
    end if;
  end loop;
  commit;
end;
/
問題：更新太慢，260 rows/s，全部更新完畢需要22天！
經過調查發現是UPDATE語句執行的效率太低，進一步的跟蹤發現，UPDATE至少90%的時間是在等待db file sequential read這個事件。按說都ROWID了，為什么還有這么多磁盤等待？再看disk reads，明白了，原來UPDATE語句產生了大量的物理讀，當然慢了。想必T1表太大了，Data Buffer裝不下，并且有其他的表跟它競爭，所以剛更新一條數據，從磁盤讀取了一個數據塊到內存，很快就被擠去出了，下次更新這個塊上的其他數據時，還得再從磁盤讀取。這樣Data Buffer Cache的效率就很低，基本沒有利用上。
怎么解決呢？最好是能按數據塊的順序更新，這樣某個數據塊里的第一行數據更新后，數據塊內的其他行就不用再從磁盤里讀取了（不太可能那么快就被擠出內存），物理讀降低了，速度肯定能加快。可是怎樣按數據塊的順序更新呢？我想到了ROWID的結構是data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，那么ROWID的順序應該就是數據塊的順序了。于是我修改了PLSQL：
alter table T1 storage(buffer_pool keep);    -- keep buffer pool size = 6GB
declare
  cursor cur_t2 is
    select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
       T2.id2, T2.curr_count, T1.rowid row_id
    from T1, T2
    where T1.id2=T2.id2
    order by T1.rowid;
  v_counter number;
begin
  v_counter := 0;
  for row_t2 in cur_t2 loop
    update T1 set curr_count=row_t2.curr_count
       where rowid=row_t2.row_id;
    v_counter := v_counter + 1;
    if (v_counter>=1000) then
      commit;
      v_counter := 0;
    end if;
  end loop;
  commit;
end;
/
alter table T1 storage(buffer_pool default);

這回更新的速度大為加快：10000 rows/s。分析跟蹤文件表明db file sequential reads和磁盤讀取變的很少。按照這個速度20個小時之內就能全部更新完了。

心得：處理的數據量并沒有減少，只是改變一下處理的順序，也可以極大地提高性能。

====================================================================
*后記4：已上生產
*后記3：試驗了KEEP的影響
(1)重新運行試驗1（不order by rowid）
     開始的語句改成：alter table T1 storage(buffer_pool keep);
     處理速度：73~74行/秒
(2)重新運行試驗2（order by rowid）：
     開始的語句改成：alter table T1 storage(buffer_pool default);
     處理速度：1萬條/秒
結論：從本次測試可以印證先前的推斷——把表的buffer_pool屬性設為keep與否，對處理速度的影響很小，以至于可以忽略。處理速度加快的原因，是因為order by rowid，按塊順序處理數據，很大程度上減少了物理讀。

*后記2：關于order by rowid的資料：
  http://rdc.taobao.com/blog/dba/html/199_oracle_rowid_order.html
   這篇文章說order by rowid導致大量的查詢物理讀。其實在本文第二個測試中也是這樣的——CURSOR的打開時間比不ORDER BY ROWID時間要長，因為多了SORT。可是這樣是值得的，因為后續有大量的UPDATE，節省的物理讀是很可觀的。

*后記1：修改了幾處錯誤：
  where T1.id1=T2.id2  => where T1.id2=T2.id2
   parallel_index(IX_T2_id2,16) => parallel_index(IX_T1_id2,16)