[20160219]关于连接顺序.txt

--今天被问一个问题，如果使用外连接的情况，连接顺序可以改变吗？我只能说我给测试看看，再回答这个问题：

1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试：
select * from dept,emp where dept.deptno=emp.deptno(+);

Plan hash value: 2251696546
--------------------------------------------------------------------------------------------------------------------
| Id | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |     6 (100)|          |       |       |          |
|   1 | MERGE JOIN OUTER            |         |     14 |   812 |     6 (17)| 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 |   532 |     4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DEPT@SEL$1
   3 - SEL$1 / DEPT@SEL$1
   5 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

--看是否可以提示改变连接顺序。

3.继续测试：
select /*+ leading(emp dept) use_hash(dept) */ * from dept,emp where dept.deptno=emp.deptno(+);

--可以发现提示无效。视乎存在外连接左右连接的情况无法改变连接顺序，这个在优化sql语句中注意。

4.继续测试，建立emp.ename索引,并且唯一。

SCOTT@book> 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

Plan hash value: 3311718186
---------------------------------------------------------------------------------------------
| Id | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |        |       |     3 (100)|          |
|   1 | NESTED LOOPS                |             |      1 |    58 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP         |      1 |    38 |     2   (0)| 00:00:01 |
|* 3 |    INDEX UNIQUE SCAN         | I_EMP_ENAME |      1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT        |      1 |    20 |     1   (0)| 00:00:01 |
|* 5 |    INDEX UNIQUE SCAN         | PK_DEPT     |      1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$C30CFC9A
   2 - SEL$C30CFC9A / EMP@SEL$1
   3 - SEL$C30CFC9A / EMP@SEL$1
   4 - SEL$C30CFC9A / DEPT@SEL$1
   5 - SEL$C30CFC9A / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMP"."ENAME"='KING')
   5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

--这个时候视乎改变了连接顺序，而且不存在外连接执行计划。但是实际上这里的+实际上多余的。如果要实现外连接应该写成：

select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)='KING';

SCOTT@book> 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
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Plan hash value: 965809594
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |        |       |     5 (100)|          |       |       |          |
|   1 | MERGE JOIN OUTER             |             |      4 |   232 |     5 (20)| 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                   |             |      1 |    38 |     3 (34)| 00:00:01 | 2048 | 2048 | 2048 (0)|
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP         |      1 |    38 |     2   (0)| 00:00:01 |       |       |          |
|* 6 |     INDEX UNIQUE SCAN         | I_EMP_ENAME |      1 |       |     1   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DEPT@SEL$1
   3 - SEL$1 / DEPT@SEL$1
   5 - SEL$1 / EMP@SEL$1
   6 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
   6 - access("EMP"."ENAME"='KING')

--如果使用提示：

select /*+ leading(emp dept) */ * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)='KING';

--可以发现连接顺序并没有改变。
--可以看出存在外连接的情况下，无法改变连接顺序的。

时间： 2024-09-20 10:39:31

[20160219]关于连接顺序.txt

[20160219]关于连接顺序.txt的相关文章

[20160330]关于连接顺序3.txt

[20160220]关于连接顺序2.txt

[20140823]12c join convert连接转换.txt

SQL优化之针对count、表的连接顺序、条件顺序、in及exist的优化_oracle

[20170825]11G备库启用DRCP连接3.txt

[20111223]索引键值在B tree索引块中的顺序.txt

Oracle性能优化学习笔记之WHERE子句中的连接顺序

[20120120]startup nomount访问参数文件的顺序.txt

[20160212]闭包传递4.txt