oracle标量子查询-创新互联
SQL> conn scott/scott
Connected.
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 a
Plan 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.
分享文章：oracle标量子查询-创新互联
文章起源：http://scjbc.cn/article/cesjci.html
B表被执行2次，返回2条数据。
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 a9 9 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 a Plan 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 a9 9 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 a Plan 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表就要被执行多少次。
标量子查询改写:
1 SQL> select * from a; ID NAME ---------- ---------- 1 a1 2 a2 SQL> select * from b; ID NAME ---------- ---------- 1 b1 2 b2 SQL> select name,(select name from b where b.id=a.id) from a; NAME (SELECTNAM ---------- ---------- a1 b1 a2 b2改写：
SQL> select a.name,b.name from a,b where a.id=b.id(+); NAME NAME ---------- ---------- a1 b1 a2 b2另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
分享文章：oracle标量子查询-创新互联
文章起源：http://scjbc.cn/article/cesjci.html
