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条数据。

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