Oracle Update执行计划原理解析与优化

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执行计划 讲解（一）