update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id); create table a as (select * from (select * from dba_objects order by object_id) where rownum<100) create table b as (select * from (select * from dba_objects order by object_id) where rownum<100) SQL> update b set object_name=object_name||'xxx'; 99 rows updated. SQL> select count(*) from a; COUNT(*) ---------- 99 子查询会被扫描99次 SQL> alter session set statistics_level=all; Session altered. SQL> update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id); 99 rows updated. SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------- SQL_ID 9n2d868fqhcq4, child number 0 ------------------------------------- update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id) Plan hash value: 1917715316 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 504 | | 1 | UPDATE | B | 1 | | 0 |00:00:00.01 | 504 | | 2 | TABLE ACCESS FULL| B | 1 | 99 | 99 |00:00:00.01 | 4 | |* 3 | TABLE ACCESS FULL| A | 99 | 1 | 99 |00:00:00.01 | 396 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("A"."OBJECT_ID"=:B1) Note ----- - dynamic sampling used for this statement (level=2) 25 rows selected. 可以看到A表被扫描了99次,类似于NL。
类似NL的update更新
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
上一篇:全表扫描引发的db file sequential read
下一篇:排序过程
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章