利用rowid快速在線更新海量數(shù)據(jù)
最近一直在折騰大表的更新問題，今天終于有了突破。興奮之余發(fā)個帖子跟大家分享一下心得，并且討論一下是否還可能進一步提高處理速度。
問題是這樣的：一張5億條記錄的表，沒有分區(qū)。由于增加了一個冗余字段，需要根據(jù)另外一張表（4.8億條）更新這個大表。下面是具體的描述：
環(huán)境：HP-UX 11i+Oracle9.2.0.8+RAID
要更新的表：T1 (id1 number, id2 number, curr_count number,.....)   --id1唯一  5億條記錄 >60GB
更新數(shù)據(jù)來源：T2 (id2 number, curr_count number)   --id2唯一  4.8億
更新邏輯：T2中的每一條記錄，都到T1中找到對應(yīng)的記錄（T2.id2=T1.id2），更新T1.curr_count=T2.curr_count
限制條件：只能在線更新（應(yīng)用程序一直在訪問這個表，所以不能用INSERT SELECT），不能占用太多系統(tǒng)資源，要求3天之內(nèi)更新完畢。
原來的做法：
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天！
經(jīng)過調(diào)查發(fā)現(xiàn)是UPDATE語句執(zhí)行的效率太低，進一步的跟蹤發(fā)現(xiàn)，UPDATE至少90%的時間是在等待db file sequential read這個事件。按說都ROWID了，為什么還有這么多磁盤等待？再看disk reads，明白了，原來UPDATE語句產(chǎn)生了大量的物理讀，當然慢了。想必T1表太大了，Data Buffer裝不下，并且有其他的表跟它競爭，所以剛更新一條數(shù)據(jù)，從磁盤讀取了一個數(shù)據(jù)塊到內(nèi)存，很快就被擠去出了，下次更新這個塊上的其他數(shù)據(jù)時，還得再從磁盤讀取。這樣Data Buffer Cache的效率就很低，基本沒有利用上。
怎么解決呢？最好是能按數(shù)據(jù)塊的順序更新，這樣某個數(shù)據(jù)塊里的第一行數(shù)據(jù)更新后，數(shù)據(jù)塊內(nèi)的其他行就不用再從磁盤里讀取了（不太可能那么快就被擠出內(nèi)存），物理讀降低了，速度肯定能加快。可是怎樣按數(shù)據(jù)塊的順序更新呢？我想到了ROWID的結(jié)構(gòu)是data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，那么ROWID的順序應(yīng)該就是數(shù)據(jù)塊的順序了。于是我修改了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個小時之內(nèi)就能全部更新完了。

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

====================================================================
*后記4：已上生產(chǎn)
*后記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萬條/秒
結(jié)論：從本次測試可以印證先前的推斷——把表的buffer_pool屬性設(shè)為keep與否，對處理速度的影響很小，以至于可以忽略。處理速度加快的原因，是因為order by rowid，按塊順序處理數(shù)據(jù)，很大程度上減少了物理讀。

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

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