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
