update的優化
在olap中,往往能看到性能很差的語句是update語句,跑半天都跑不過去,雖然語句可以千變萬化,但是優化起來還是有規可循的。
--測試表:
drop table t1;
drop table t2;
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
--原始update語句
update t1 set t1.owner=(select t2.owner from t2 where t2.object_id=t1.object_id);
--683s
rollback;
執行計劃如下:
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 69746 | 2043K| 150 (1)| 00:00:03 |
| 1 | UPDATE | T1 | | | | |
| 2 | TABLE ACCESS FULL| T1 | 69746 | 2043K| 150 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL| T2 | 546 | 16380 | 150 (1)| 00:00:03 |
---------------------------------------------------------------------------
兩個小表居然花了10多分鐘,至於為什麼這麼慢,我就不說了,要研究的話可以看下語句真實的執行計劃,請看《如何獲取執行計劃》這篇文章,我只說一下優化的方法。
--1建立組合索引
create index idx on t2(object_id,owner);
update t1 set t1.owner=(select t2.owner from t2 where t2.object_id=t1.object_id);
--0.7s
rollback;
因為t2只用到了兩個字段的數據,object_id和owner,考慮將起建立組合索引,掃描的時候只需要掃描索引中的數據即可
只能用object_id,owner的順序才能讓索引走range scan提高效率,owner,object_id的順序是錯的。
--2plsql分批update
declare
v_count number;
cursor c is
select t1.rowid row_id,t2.object_id,t2.owner from t1,t2 where t1.object_id=t2.object_id;
begin
v_count:=0;
for x in c loop
update t1 set t1.owner=x.owner where rowid=x.row_id;
v_count:=v_count+1;
if (v_count>=1000) then
commit;
v_count:=0;
end if;
end loop;
commit;
end;
--1.9s
通過rowid定位update的數據,避免每次update都走全表掃描。
--3merger into優化(undo較多,怕死事務恢復)
merge into t1
using t2
on (t1.object_id=t2.object_id)
when matched then
update set t1.owner=t2.owner;
--0.84s
總結:
直接update大表是最垃圾的寫法。
方法1:當表較小時,效率較高。可以這樣用。當表大時,頻繁掃描索引,會產生熱點塊,會產生鎖等待:cbc latch。不推薦。
方法2:當表大時,推薦用這種方法,分批提交,避免大事務。不然大事務一直不提交占用回滾段,容易報回滾段不足的錯。這也是為什麼有時候跑數跑不過,有時候又沒問題的根本原因。不是oracle的問題,是語句的問題。
方法3:如果你用set autotrace on的方法測試,你會發現merge產生的undo是非常多的。一旦斷電或者其他原因造成數據庫down機,那麼就完了。。。數據庫為了保證數據的一致性,啟動之後要讀undo進行恢復,讀undo是單塊讀,非常慢,如果多塊讀參數為16,你merge了1個小時還沒完成,突然down機了,那麼恢復起來就要16個小時才能恢復完,數據庫16個小時不能工作那就坑爹了。。。