[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。