类似NL的update更新
2024-09-10 10:04:32
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。
最新文章
- JAVA匿名内部类
- 转载:oracle null处理
- 全文检索引擎Solr系列——整合MySQL、MongoDB
- Java文件内容的复制
- Python学习笔记-Day3-python内置函数
- 【BZOJ3262】 陌上花开
- struts2文件上传,文件类型 allowedTypes
- php curl 中的gzip压缩性能测试
- web中webAppRootKey作用
- bmob云 实现注册和登录的功能
- H264格式具体说明
- C# 处理图片 不规则图形裁剪
- 简说JS中的循环部分
- 在nuxt中加入element-ui插件遇到的问题
- VisualStudio2017下ASP.NET CORE的TagHelper智能提示不能使用的解决办法
- js改变数组的两个元素的位子,互换、置顶
- SignalR 自寄宿
- Opencv中Mat矩阵相乘——点乘、dot、mul运算详解
- php文件包含漏洞(利用phpinfo)复现
- Linux软件源书写格式解析及本地yum源制作