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.
B表被执行2次,返回2条数据。
创新互联专注于企业全网整合营销推广、网站重做改版、阳东网站定制设计、自适应品牌网站建设、HTML5、商城网站建设、集团公司官网建设、外贸网站制作、高端网站制作、响应式网页设计等建站业务,价格优惠性价比高,为阳东等各大城市提供网站开发制作服务。
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 a Plan 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 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
文章标题:oracle标量子查询
当前路径:http://myzitong.com/article/jeesjd.html