[20140125]關於標量子查詢.txt
最近一致在最佳化一個垃圾專案，我發現一個奇怪的想象，就是開發很喜歡使用標量子查詢，我發現這個東西像傳染病一樣，一個人使用其
他人也跟著仿效，而不考慮具體的使用場合。還有一些出現在檢視裡面。
我想透過一些例子來說明情況：
@ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--標量子查詢例子：
SCOTT@test01p> select emp.*,(select dname from dept where dept.deptno=emp.deptno) ename from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ENAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 RESEARCH
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 SALES
...
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID du01z0rvgas7m, child number 0
-------------------------------------
select emp.*,(select dname from dept where dept.deptno=emp.deptno)
ename from emp
Plan hash value: 2981343222
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 6 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 0 (0)|
| 3 | TABLE ACCESS FULL | EMP | 14 | 3 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
--可以發現要存取dept表透過PK_DEPT來取，如果emp很大，效率不高，邏輯讀會很大。
--這樣寫最大的好處是如果連線的表很多的情況下，看上去更加簡單一些。
--而且可以發現一些缺點，就是子查詢僅僅查詢一個欄位，不能包括多個欄位。
SCOTT@test01p> select emp.*,(select dname,loc from dept where dept.deptno=emp.deptno) from emp;
select emp.*,(select dname,loc from dept where dept.deptno=emp.no) from emp
*
ERROR at line 1:
ORA-00913: too many values
--如果寫成這樣效率更低。
SCOTT@test01p> set autot traceonly ;
select emp.*,(select dname from dept where dept.deptno=emp.deptno) dname,
(select loc from dept where dept.deptno=emp.deptno) loc
from emp;
Execution Plan
---------------------------
Plan hash value: 3707356765
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 546 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 546 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
4 - access("DEPT"."DEPTNO"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
1933 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
--邏輯讀達到了18個。
--而如果寫成如下：
SCOTT@test01p> select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 826 | 5 (0)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 826 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 546 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 546 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1809 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
--僅僅11個邏輯讀。
--補充一些資訊在11g下的測試
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> alter session set statistics_level=all;
Session altered.
select emp.*,(select dname from dept where dept.deptno=emp.deptno) dname,
(select loc from dept where dept.deptno=emp.deptno) loc
from emp;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dmgh9v95g9010, child number 0
-------------------------------------
select emp.*,(select dname from dept where dept.deptno=emp.deptno)
dname, (select loc from dept where dept.deptno=emp.deptno) loc from emp
Plan hash value: 3707356765
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 14 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 | 2 (0)| 3 |00:00:00.01 | 5 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 1 | 1 (0)| 3 |00:00:00.01 | 2 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 | 2 (0)| 3 |00:00:00.01 | 5 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 1 | 1 (0)| 3 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
4 - access("DEPT"."DEPTNO"=:B1)
--可以發現1個小小的問題,11G下,標量子查詢的cost不計算.導致最後的costs很低.而且最後邏輯度的數量也計算錯誤,應該是7+5+5=17.
select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 51ag9ppruqa9u, child number 0
-------------------------------------
select emp.*,dept.dname,dept.loc from emp,dept where
emp.deptno=dept.deptno
Plan hash value: 615168685
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 14 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 8 (13)| 14 |00:00:00.01 | 15 | 1023K| 1023K| 746K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 6 | 4 (0)| 5 |00:00:00.01 | 8 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
總結:
使用標量子查詢一定要注意場合,僅僅在返回行數很少的時候才有效.而不是到處亂用.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1075477/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211220]關於標量子查詢問題.txt
- 標量子查詢
- 標量子查詢（二）
- 標量子查詢(一)
- [20150709]慎用標量子查詢.txt
- [20200325]慎用標量子查詢.txt
- 用WITH…AS改寫標量子查詢
- [20150727]使用標量子查詢小問題.txt
- 都是標量子查詢惹的禍
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- MYSQL count標量子查詢改left joinMySql
- 用LEFT JOIN優化標量子查詢優化
- [20211214]18c標量子查詢unnest.txt
- [20180612]函式與標量子查詢10.txt函式
- [20180626]函式與標量子查詢14.txt函式
- [20180602]函式與標量子查詢3.txt函式
- 20180601]函式與標量子查詢2.txt函式
- [20180611]函式與標量子查詢9.txt函式
- [20180607]函式與標量子查詢8.txt函式
- [20180602]函式與標量子查詢4.txt函式
- Oracle常用抓取SQL-標量子查詢等OracleSQL
- 帶彙總的標量子查詢改寫
- 標量子查詢優化（用group by 代替distinct）優化
- mysql 標量子查詢和現金盤程式製作非法子查詢MySql
- 影響Oracle標量子查詢效能的三個因素Oracle
- [20210202]計算標量子查詢快取數量2.txt快取
- [20210201]19c計算標量子查詢快取數量.txt快取
- 【TUNE_ORACLE】列出有標量子查詢的SQL參考OracleSQL
- 效能為王：SQL標量子查詢的優化案例分析SQL優化
- GreatSQL 最佳化技巧：將 MINUS 改寫為標量子查詢SQL
- 關於批次分頁查詢
- 關於查詢塊query blockBloC
- [20180625]函式與標量子查詢13(補充)函式
- 關於oracle的空間查詢Oracle
- 關於index檔案呼叫查詢Index
- 閃回（關於閃回查詢）
- 遊標查詢
- 關於MySQL 通用查詢日誌和慢查詢日誌分析MySql