Oracle Update執行計畫原理解析與最佳化

當使用update，大多數情況下只有一個子節點，只有當set子句中使用子查詢時，它才能有兩個以上的節點，如果它只有一個節點的情況下，類似於過濾操作（探索執行計畫中filter的原理），如下實驗所示，步驟2首先掃描一次，當步驟2每返回一條資料步驟3、4、5都掃描一次，所以步驟3、4、5掃描了999次。

當update下面有多個節點時，大家可以看到相當於是多個nestedloop（嵌套迴圈串連(nested loops join)原理），如果你操作的資料量大，則效能可能會有問題，這種情境下，使用merge into(用merge into進行效能最佳化)最佳化，可以把執行計畫變為hash join（雜湊串連(hash join) 原理）,表都只要掃描一次，效能會有提升。

SQL> create table test as select * from dba_objects where rownum <1000;
表已建立。
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL 過程已成功完成。
SQL> alter session set statistics_level=all;
會話已更改。

SQL> update test t1 set owner=(select owner from test t2
where t1.object_id=t2.object_id),
object_name =(select object_name from test t2
where t1.object_id=t2.object_id),
object_type =(select object_type from test t2
where t1.object_id=t2.object_id);
已更新999行。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 6d1m5j0qsg875, child number 0
-------------------------------------
update test t1 set owner=(select owner from test t2 where
t1.object_id=t2.object_id), object_name =(select object_name from test
t2 where t1.object_id=t2.object_id), object_type =(select
object_type from test t2 where t1.object_id=t2.object_id)
Plan hash value: 1849821134
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | UPDATE | TEST | 1 | | 0 |00:00:00.25 | 46019 |
| 2 | TABLE ACCESS FULL| TEST | 1 | 999 | 999 |00:00:00.01 | 15 |
|* 3 | TABLE ACCESS FULL| TEST | 999 | 1 | 999 |00:00:00.08 | 14985 |
|* 4 | TABLE ACCESS FULL| TEST | 999 | 1 | 999 |00:00:00.08 | 14985 |
|* 5 | TABLE ACCESS FULL| TEST | 999 | 1 | 999 |00:00:00.08 | 14985 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."OBJECT_ID"=:B1)
4 - filter("T2"."OBJECT_ID"=:B1)
5 - filter("T2"."OBJECT_ID"=:B1)

Oracle執行計畫 講解（一）