利用rowid 進行大批量資料更新 -- 轉帖

--轉帖請註明作者和出處: fromeast http://www.itpub.net/thread-1052077-1-1.html
最近一直在折騰大表的更新問題，今天終於有了突破。興奮之餘發個帖子跟大家分享一下心得，並且討論一下是否還可能進一步提高處理速度。
問題是這樣的：一張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)