oracle标量子查询
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，SQL> conn scott/scottConnected.SQL> create table a (id int,name varchar2(10));Table created.SQL> cre
千家信息网最后更新 2025年11月07日oracle标量子查询
SQL> conn scott/scottConnected.SQL> create table a (id int,name varchar2(10));Table created.SQL> create table b (id int,name varchar2(10));Table created.SQL> insert into a values(1,'a1');1 row created.SQL> insert into a values(2,'a2');1 row created.SQL> insert into b values(1,'b1');1 row created.SQL> insert into b values(2,'b2');1 row created.SQL> commit;Commit complete.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 8rv825dykpx1m, child number 1-------------------------------------select a.*,(select name from b where b.id=a.id) from aPlan hash value: 2657529235------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 8 ||* 1 | TABLE ACCESS FULL| B | 2 | 1 | 2 |00:00:00.01 | 14 || 2 | TABLE ACCESS FULL| A | 1 | 2 | 2 |00:00:00.01 | 8 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("B"."ID"=:B1)Note----- - dynamic sampling used for this statement (level=2)23 rows selected.B表被执行2次，返回2条数据。
SQL> insert into a values(3,'a3');1 row created.SQL> commit;Commit complete.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 9rufvg18a2vfq, child number 0-------------------------------------select a.*,(select name from b where b.id=a.id) from aPlan hash value: 2657529235------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 8 ||* 1 | TABLE ACCESS FULL| B | 3 | 1 | 2 |00:00:00.01 | 21 || 2 | TABLE ACCESS FULL| A | 1 | 3 | 3 |00:00:00.01 | 8 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("B"."ID"=:B1)Note----- - dynamic sampling used for this statement (level=2)23 rows selected.B表被执行3次，返回2条数据。
SQL> insert into a values(4,'a4');1 row created.SQL> insert into a values(5,'a5');1 row created.SQL> insert into a values(6,'a6');1 row created.SQL> insert into a values(7,'a7');1 row created.SQL> insert into a values(8,'a8');1 row created.SQL> insert into a values(9,'a9');1 row created.SQL> commit;Commit complete.SQL> select a.*,(select name from b where b.id=a.id) from a; ID NAME (SELECTNAM---------- ---------- ---------- 1 a1 b1 2 a2 b2 3 a3 4 a4 5 a5 6 a6 7 a7 8 a8 9 a99 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 8rv825dykpx1m, child number 1-------------------------------------select a.*,(select name from b where b.id=a.id) from aPlan hash value: 2657529235------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 8 ||* 1 | TABLE ACCESS FULL| B | 9 | 1 | 2 |00:00:00.01 | 63 || 2 | TABLE ACCESS FULL| A | 1 | 2 | 9 |00:00:00.01 | 8 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("B"."ID"=:B1)Note----- - dynamic sampling used for this statement (level=2)23 rows selected.B表被执行9次，返回2行数据，说明a表向b传值，能匹配上就返回，匹配不上就返回null
SQL> update b set name='b1';2 rows updated.SQL> commit;Commit complete.SQL> select a.*,(select name from b where b.id=a.id) from a; ID NAME (SELECTNAM---------- ---------- ---------- 1 a1 b1 2 a2 b1 3 a3 4 a4 5 a5 6 a6 7 a7 8 a8 9 a99 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 8rv825dykpx1m, child number 1-------------------------------------select a.*,(select name from b where b.id=a.id) from aPlan hash value: 2657529235------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 8 ||* 1 | TABLE ACCESS FULL| B | 9 | 1 | 2 |00:00:00.01 | 63 || 2 | TABLE ACCESS FULL| A | 1 | 2 | 9 |00:00:00.01 | 8 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("B"."ID"=:B1)Note----- - dynamic sampling used for this statement (level=2)23 rows selected.理想状态下，a.id为主键，没有重复值，那么a表返回多少行，b表就要被执行多少次。
标量子查询改写:
1SQL> select * from a; ID NAME---------- ---------- 1 a1 2 a2SQL> select * from b; ID NAME---------- ---------- 1 b1 2 b2SQL> select name,(select name from b where b.id=a.id) from a;NAME (SELECTNAM---------- ----------a1 b1a2 b2
改写：
SQL> select a.name,b.name from a,b where a.id=b.id(+);NAME NAME---------- ----------a1 b1a2 b2
数据
量子
查询
状态
理想
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
文件服务器最大存储量
网络安全 不传谣 信谣
初中没计算机软件开发
导入相关数据库python
数据库筛选日期
黑龙江龙采科技集团软件开发
经营项目软件开发包含哪些
2021软考数据库答案上午
仓储物联网数据库
常州java软件开发培训
误造怎么单独开服务器
河南采购管理软件开发
网络安全覆盖全域视频
删除数据库语句
数据库数值范围
好人榜数据库如何建立
抚顺一手网络技术公司
请求服务器前端开始游戏失败
数据库中为什么需要使用索引
网络安全运营者是什么
徐州宏源软件开发
自己建立数据库集群
双路服务器算力如何计算
专科软件开发专业排名
主要收录会议论文的数据库是
软件开发为啥学不懂
北京启名星辰网络安全
宜兴智能软件开发价格
网络安全制度教育培训演练
hp服务器插上网线不能自动连接
