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条数据。
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
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
