彻底搞懂oracle的标量子查询

ewr32

oracle标量子查询和自定义函数有时用起来比较方便，而且开发人员也经常使用，数据量小还无所谓，数据量大，往往存在性能问题。
以下测试帮助大家彻底搞懂标量子查询。

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> @getlvall
Session altered.
SQL> select a.*,(select name from b where b.id=a.id) from a;
        ID NAME                 (SELECTNAMEFROMBWHER
---------- -------------------- --------------------
         1 a1                   b1
         2 a2                   b2
SQL> @getplanspe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8rv825dykpx1m, 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 |
------------------------------------------------------------------------------------
|*  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
22 rows selected.
由上面的执行计划可以知道，b表执行2次，返回2行
SQL> insert into a values (3,'a3');
1 row created.
SQL> commit;
Commit complete.
SQL> select a.*,(select name from b where b.id=a.id) from a;
        ID NAME                 (SELECTNAMEFROMBWHER
---------- -------------------- --------------------
         1 a1                   b1
         2 a2                   b2
         3 a3
SQL> @getplanspe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8rv825dykpx1m, 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 |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| B    |      3 |      1 |      2 |00:00:00.01 |      21 |
|   2 |  TABLE ACCESS FULL| A    |      1 |      2 |      3 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"."ID"=:B1)
Note
-----
   - dynamic sampling used for this statement
22 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                 (SELECTNAMEFROMBWHER
---------- -------------------- --------------------
         1 a1                   b1
         2 a2                   b2
         3 a3
         4 a4
         5 a5
         6 a6
         7 a7
         8 a8
         9 a9
9 rows selected.
SQL> @getplanspe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8rv825dykpx1m, 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 |
------------------------------------------------------------------------------------
|*  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
22 rows selected.
由上面的执行计划可以知道，b表执行9次，返回2行
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                 (SELECTNAMEFROMBWHER
---------- -------------------- --------------------
         1 a1                   b1
         2 a2                   b1
         3 a3
         4 a4
         5 a5
         6 a6
         7 a7
         8 a8
         9 a9
9 rows selected.
SQL> @getplanspe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8rv825dykpx1m, 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 |
------------------------------------------------------------------------------------
|*  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
22 rows selected.
由上面的执行计划可以知道，b表执行2次，返回2行
SQL> insert into b values (3,'b1');
1 row created.
SQL> insert into b values (4,'b1');
1 row created.
SQL> insert into b values (5,'b1');
1 row created.
insert into b values (6,'b1');b1');
1 row created.
SQL> insert into b values (7,'b1');
1 row created.
SQL> insert into b values (8,'b1');
1 row created.
SQL> insert into b values (9,'b1');
1 row created.
SQL> commit;
Commit complete.
SQL> select a.*,(select name from b where b.id=a.id) from a;
        ID NAME                 (SELECTNAMEFROMBWHER
---------- -------------------- --------------------
         1 a1                   b1
         2 a2                   b1
         3 a3                   b1
         4 a4                   b1
         5 a5                   b1
         6 a6                   b1
         7 a7                   b1
         8 a8                   b1
         9 a9                   b1
9 rows selected.
SQL> @getplanspe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8rv825dykpx1m, 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 |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| B    |      9 |      1 |      9 |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
22 rows selected.
b.name字段全部为‘b1’，由上面的执行计划可以知道，b表执行9次，返回9行
SQL> update a set id=1;
9 rows updated.
SQL> commit;
Commit complete.
SQL> select * from a;
        ID NAME
---------- --------------------
         1 a1
         1 a2
         1 a3
         1 a4
         1 a5
         1 a6
         1 a7
         1 a8
         1 a9
9 rows selected.
SQL> select * from b;
        ID NAME
---------- --------------------
         1 b1
         2 b1
         3 b1
         4 b1
         5 b1
         6 b1
         7 b1
         8 b1
         9 b1
9 rows selected.
SQL> select a.*,(select name from b where b.id=a.id) from a;
        ID NAME                 (SELECTNAMEFROMBWHER
---------- -------------------- --------------------
         1 a1                   b1
         1 a2                   b1
         1 a3                   b1
         1 a4                   b1
         1 a5                   b1
         1 a6                   b1
         1 a7                   b1
         1 a8                   b1
         1 a9                   b1
9 rows selected.
SQL> @getplanspe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8rv825dykpx1m, 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 |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| B    |      1 |      1 |      1 |00:00:00.01 |       7 |
|   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
22 rows selected.
SQL>
关联字段a.id全部为1，a表有9行，标量子查询相当于执行9次select name from b where b.id=1 ，oracle也不傻，starts=1，说明只执行了1次。
总结：
理想状态下，a.id为主键，没有重复值，那么a表返回多少行，b表就要被执行多少次。
特殊情况下，a.id的distinct值只有n个，那么b表只执行n次。