oracle标量子查询
发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，SQL> conn scott/scottConnected.SQL> create table a (id int,name varchar2(10));Table created.SQL> cre
千家信息网最后更新 2026年11月01日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安全错误
数据库的锁怎样保障安全
科技互联网公司装修
金融科技还是互联网
金融科技 互联网金融
百单互联网科技有限公司
融势互联网科技
广州紫鲸互联网科技
互联网科技论坛
蓝色互联网科技
云创客互联网科技公司
创业天下互联网科技有限公司
随着互联网科技发展
互联网5g黑科技有哪些
河南住房之家互联网科技
企晟互联网信息科技有限公司
翼展科技 首家互联网医院
高科技互联网套路
启辰互联网科技
版纳互联网科技选哪家
互联网科技和医药行业
互联网金融金融科技
云之行互联网科技
江苏根荣互联网科技
中国互联网科技股
云电互联网科技
博兴的互联网电子科技公司
互联网科技有哪些公司
互联网科技人工智能实验室
区块链结合互联网科技
合肥互联网科技公司公章图片
上海互联网科技公司ppt
