[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")

總結:
使用標量子查詢一定要注意場合,僅僅在返回行數很少的時候才有效.而不是到處亂用.