[20160220]关于连接顺序2.txt
--前几天被问一个问题，如果使用外连接的情况，连接顺序可以改变吗？我只能说我给测试看看，再回答这个问题：
--链接测试已经说明存在外连接的情况下，无法改变连接顺序的。
--今天想想忘记考虑全连接的情况,继续测试:
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
2.首先看看上次测试执行的语句:
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename='KING';
--我提到实际上上面的语句没有使用外连接,通过10053事件看看:
SCOTT@test01p> @10053on 12
Session altered.
SCOTT@test01p> Select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename='KING';
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
SCOTT@test01p> @10053off
Session altered.
--看看转储,可以发现最终转换
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DEPT"."DEPTNO" "DEPTNO","DEPT"."DNAME" "DNAME","DEPT"."LOC" "LOC","EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR"
"MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO"
FROM "SCOTT"."DEPT" "DEPT","SCOTT"."EMP" "EMP"
WHERE "DEPT"."DEPTNO" = "EMP"."DEPTNO"
AND "EMP"."ENAME" = 'KING'
kkoqbc: optimizing query block SEL$C30CFC9A (#0)
--可以发现外连接实际上是取消了.煮为了显示的需要,做了格式化处理.
3.看看全连接的情况:
select /*+ leading(emp dept) */ * from dept full join emp on dept.deptno=emp.deptno ;
select /*+ leading(emp dept) */ * from emp full join dept on dept.deptno=emp.deptno ;
Plan hash value: 51889263
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
| 1 | VIEW | VW_FOJ_0 | 15 | 1755 | 6 (0)| 00:00:01 | | | |
|* 2 | HASH JOIN FULL OUTER| | 15 | 870 | 6 (0)| 00:00:01 | 1321K| 1321K| 1060K (0)|
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 | | | |
| 4 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / from$_subquery$_003@SEL$2
2 - SEL$1
3 - SEL$1 / DEPT@SEL$1
4 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
--以上2种写法执行计划都是一样.再来看看10053事件.
SCOTT@test01p> @ 10053on 12
Session altered.
Select /*+ leading(emp dept) */ * from dept full join emp on dept.deptno=emp.deptno ;
SCOTT@test01p> @ 10053off
Session altered.
--检查转储文件:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ LEADING ("EMP" "DEPT") */ "VW_FOJ_0"."DEPTNO_0" "DEPTNO","VW_FOJ_0"."DNAME_1"
"DNAME","VW_FOJ_0"."LOC_2" "LOC","VW_FOJ_0"."EMPNO_3" "EMPNO","VW_FOJ_0"."ENAME_4" "ENAME","VW_FOJ_0"."JOB_5"
"JOB","VW_FOJ_0"."MGR_6" "MGR","VW_FOJ_0"."HIREDATE_7" "HIREDATE","VW_FOJ_0"."SAL_8" "SAL","VW_FOJ_0"."COMM_9"
"COMM","VW_FOJ_0"."DEPTNO_10" "DEPTNO"
FROM (
SELECT "DEPT"."DEPTNO" "DEPTNO_0","DEPT"."DNAME" "DNAME_1","DEPT"."LOC" "LOC_2","EMP"."EMPNO"
"EMPNO_3","EMP"."ENAME" "ENAME_4","EMP"."JOB" "JOB_5","EMP"."MGR" "MGR_6","EMP"."HIREDATE"
"HIREDATE_7","EMP"."SAL" "SAL_8","EMP"."COMM" "COMM_9","EMP"."DEPTNO" "DEPTNO_10"
FROM "SCOTT"."EMP" "EMP"
FULL OUTER JOIN "SCOTT"."DEPT" "DEPT"
ON "DEPT"."DEPTNO" = "EMP"."DEPTNO") "VW_FOJ_0"
kkoqbc: optimizing query block SEL$1 (#0)
--提示无效,理论讲这个那个在前在后都可以.
SCOTT@test01p> EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>user, TABNAME=>'dept', NUMROWS=> 1000,NO_INVALIDATE=>false);
PL/SQL procedure successfully completed.
Select /*+ leading(emp dept) */ * from dept full outer join emp on dept.deptno=emp.deptno ;
Plan hash value: 3194568465
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
| 1 | VIEW | VW_FOJ_0 | 3750 | 428K| 6 (0)| 00:00:01 | | | |
|* 2 | HASH JOIN FULL OUTER| | 3750 | 212K| 6 (0)| 00:00:01 | 1048K| 1048K| 646K (0)|
| 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
| 4 | TABLE ACCESS FULL | DEPT | 1000 | 20000 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / from$_subquery$_003@SEL$2
2 - SEL$1
3 - SEL$1 / EMP@SEL$1
4 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
--不知道使用什么提示改变全连接顺序.