[20150727]使用標量子查詢小問題.txt

[20150727]使用標量子查詢小問題.txt

--最近一段時間一直在做最佳化,仔細看我前面的blog，不主張使用標量子查詢，實際上還是有一些小細節要注意。

1.測試環境:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> alter session set statistics_level=all;
Session altered.

2.開始測試：

SCOTT@test> select emp.*,(select dname from dept where dept.deptno=emp.deptno) dname from emp ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
      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
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30 SALES
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20 RESEARCH
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30 SALES
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30 SALES
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10 ACCOUNTING
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20 RESEARCH
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10 ACCOUNTING
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30 SALES
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20 RESEARCH
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30 SALES
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20 RESEARCH
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10 ACCOUNTING
14 rows selected.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  572663gdmp3jm, child number 0
-------------------------------------
select emp.*,(select dname from dept where dept.deptno=emp.deptno)
dname from emp
Plan hash value: 2981343222
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | 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 |    13 |     1   (0)| 00:00:01 |      3 |00:00:00.01 |       5 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |       |     0   (0)|          |      3 |00:00:00.01 |       2 |
|   3 |  TABLE ACCESS FULL          | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2 / DEPT@SEL$2
   2 - SEL$2 / DEPT@SEL$2
   3 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"=:B1)

--可以發現id=2,starts = 3,總的buffers=5,總共12個邏輯讀。(注:不知道為什麼少1次，共3次，每次2個邏輯讀，應該是6.我的理解。)

3.繼續測試：
SCOTT@test> create table empx as select * from emp ;
Table created.

SCOTT@test> update empx set deptno=20 ;
14 rows updated.

SCOTT@test> commit ;
Commit complete.

--換成empx繼續測試:
SCOTT@test> select empx.*,(select dname from dept where dept.deptno=empx.deptno) dname from empx ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
      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         20 RESEARCH
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         20 RESEARCH
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20 RESEARCH
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         20 RESEARCH
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    20 RESEARCH
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    20 RESEARCH
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20 RESEARCH
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    20 RESEARCH
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         20 RESEARCH
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20 RESEARCH
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    20 RESEARCH
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20 RESEARCH
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    20 RESEARCH
14 rows selected.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  56k9g55sap2h9, child number 0
-------------------------------------
select empx.*,(select dname from dept where dept.deptno=empx.deptno)
dname from empx
Plan hash value: 3630249127
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |     3 (100)|          |     14 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |    13 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|   3 |  TABLE ACCESS FULL          | EMPX    |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2 / DEPT@SEL$2
   2 - SEL$2 / DEPT@SEL$2
   3 - SEL$1 / EMPX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"=:B1)

--可以發現id=2,starts =1,總的buffers=2,總共5個邏輯讀。如果標量查詢每次返回都一樣，oracle僅僅執行1次。邏輯讀一樣很小。

--至少說明一點如果標量子查詢的變化很少，總的邏輯讀不會太高。當然我個人堅持認為儘量少用標量子查詢，它有許多限制，主要是開
--發許多不瞭解oracle。