oracle执行计划详解并优化,OracleUpdate执行计划原理解析与优化

当使用update，大多数情况下只有一个子节点，只有当set子句中使用子查询时，它才能有两个以上的节点，如果它只有一个节点的情况下

当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执行计划 讲解(一)

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网