oracle標量子查詢和自定義函數(shù)有時用起來比較方便，而且開發(fā)人員也經(jīng)常使用，數(shù)據(jù)量小還無所謂，數(shù)據(jù)量大，往往存在性能問題。 以下測試幫助大家徹底搞懂標量子查詢。 SQL create table a (id int,name varchar2(10)); Table created. SQL create table b (i
oracle標量子查詢和自定義函數(shù)有時用起來比較方便，而且開發(fā)人員也經(jīng)常使用，數(shù)據(jù)量小還無所謂，數(shù)據(jù)量大，往往存在性能問題。
以下測試幫助大家徹底搞懂標量子查詢。
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.
由上面的執(zhí)行計劃可以知道，b表執(zhí)行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.
由上面的執(zhí)行計劃可以知道，b表執(zhí)行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.
由上面的執(zhí)行計劃可以知道，b表執(zhí)行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.
由上面的執(zhí)行計劃可以知道，b表執(zhí)行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’，由上面的執(zhí)行計劃可以知道，b表執(zhí)行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>
關(guān)聯(lián)字段a.id全部為1，a表有9行，標量子查詢相當于執(zhí)行9次select name from b where b.id=1 ，oracle也不傻，starts=1，說明只執(zhí)行了1次。
總結(jié)：
理想狀態(tài)下，a.id為主鍵，沒有重復值，那么a表返回多少行，b表就要被執(zhí)行多少次。
特殊情況下，a.id的distinct值只有n個，那么b表只執(zhí)行n次。
聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
