ZT 利用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)