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

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
-------------------------------------