oracle标量子查询和自定义函数有时用起来比较方便，而且开发人员也经常使用，数据量小还无所谓，数据量大，往往存在性能问题。 以下测试帮助大家彻底搞懂标量子查询。 sql create table a (id int,name varchar2(10)); table created. sql create table b (i
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次。