Oracle Hint 学习之三

与表连接顺序的相关hint：

执行顺序emp-jobs-dept

sql> select e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d where e.empno=j.empno and e.deptno=d.deptno and d.loc='CHICAGo' order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 477715418
-----------------------------------------------------------------------------------------------
| Id  | Operation       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     5 |   235 |     9  (23)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |     5 |   235 |     9  (23)| 00:00:01 |
|*  2 |   HASH JOIN       |      |     5 |   235 |     8  (13)| 00:00:01 |
|   3 |    MERGE JOIN       |      |    14 |   504 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP      |    14 |   238 |     2(0)| 00:00:01 |
|   5 |      INDEX FULL SCAN       | PK_EMP       |    14 |       |     1(0)| 00:00:01 |
|*  6 |     SORT JOIN       |      |    14 |   266 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | JOBS      |    14 |   266 |     3(0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID | DEPT      |     1 |    11 |     2(0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN       | IDX_DEPT_LOC |     1 |       |     1(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate InfORMation (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")
   6 - access("E"."EMPNO"="J"."EMPNO")
       filter("E"."EMPNO"="J"."EMPNO")
   9 - access("D"."LOC"='CHICAGO')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
  7  consistent gets
  0  physical reads
  0  redo size
898  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  2  sorts (memory)
  0  sorts (disk)
  6  rows processed

修改from后表的顺序：执行顺序变成emp-dept-jobs

SQL> select e.ename,j.job,e.sal,d.deptno from emp e,dept d,jobs j where e.empno=j.empno and e.deptno=d.deptno and d.loc='CHICAGO' order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3709357593
------------------------------------------------------------------------------------------------
| Id  | Operation| Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT|       |     5 |   235 |     9(23)| 00:00:01 |
|   1 |  SORT ORDER BY|       |     5 |   235 |     9(23)| 00:00:01 |
|*  2 |   HASH JOIN|       |     5 |   235 |     8(13)| 00:00:01 |
|   3 |    MERGE JOIN|       |     5 |   140 |     5(20)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | EMP       |    14 |   238 |     2 (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN| IDX_EMP_DEPT |    14 |       |     1 (0)| 00:00:01 |
|*  6 |     SORT JOIN|       |     1 |    11 |     3(34)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| DEPT       |     1 |    11 |     2 (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN| IDX_DEPT_LOC |     1 |       |     1 (0)| 00:00:01 |
|   9 |    TABLE ACCESS FULL| JOBS       |    14 |   266 |     3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Leading hint：是针对多个目标表的hint，它的含义是让优化器将我们指定的多个表的连接结果作为目标sql表连接过程中的驱动结果集，并且将leading hint中从左到右出现的第一个目标表作为整个表连接过程中的首个驱动表：（emp_temp--emp--dept-jobs）对于没有指定的，优化器可以调整，而ordered表连接顺序被指定死了。

SQL> select e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d,emp_temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.loc='CHICAGO' order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3130730953
-----------------------------------------------------------------------------------------------
| Id  | Operation       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     5 |   270 |    12(9)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |     5 |   270 |    12(9)| 00:00:01 |
|*  2 |   HASH JOIN       |      |     5 |   270 |    11(0)| 00:00:01 |
|*  3 |    HASH JOIN       |      |     5 |   175 |     8(0)| 00:00:01 |
|*  4 |     HASH JOIN       |      |    14 |   336 |     6(0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL       | EMP_TEMP     |    14 |    98 |     3(0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL       | EMP      |    14 |   238 |     3(0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |    11 |     2(0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN       | IDX_DEPT_LOC |     1 |       |     1(0)| 00:00:01 |
|   9 |    TABLE ACCESS FULL       | JOBS      |    14 |   266 |     3(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

USE_MERGE：中指定的目标表应该是排序合并连接的中的被驱动表：

SQL> select  * from emp e,dept d where e.deptno=d.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4082513813
---------------------------------------------------------------------------------------------
| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    | 14 |812 |  6  (17)| 00:00:01 |
|   1 |  MERGE JOIN     |    | 14 |812 |  6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    | 14 |532 |  2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | IDX_EMP_DEPT | 14 |    |  1   (0)| 00:00:01 |
|*  4 |   SORT JOIN     |    |  4 | 80 |  4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL     | DEPT    |  4 | 80 |  3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

SQL> select e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d,emp_temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.loc='CHICAGO' order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2730522951
--------------------------------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |     5 |   270 |    15  (34)| 00:00:01 |
|   1 |  MERGE JOIN  | |     5 |   270 |    15  (34)| 00:00:01 |
|   2 |   SORT JOIN  | |     5 |   235 |    11  (37)| 00:00:01 |
|   3 |    MERGE JOIN  | |     5 |   235 |    10  (30)| 00:00:01 |
|   4 |     SORT JOIN  | |     5 |   140 |     6  (34)| 00:00:01 |
|   5 |      MERGE JOIN   | |     5 |   140 |     5  (20)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID | EMP  |    14 |   238 |     2   (0)| 00:00:01 |
|   7 |        INDEX FULL SCAN  | IDX_EMP_DEPT |    14 | |     1   (0)| 00:00:01 |
|*  8 |       SORT JOIN   | |     1 |    11 |     3  (34)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID| DEPT |     1 |    11 |     2   (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN  | IDX_DEPT_LOC |     1 | |     1   (0)| 00:00:01 |
|* 11 |     SORT JOIN  | |    14 |   266 |     4  (25)| 00:00:01 |
|  12 |      TABLE ACCESS FULL  | JOBS |    14 |   266 |     3   (0)| 00:00:01 |
|* 13 |   SORT JOIN  | |    14 |    98 |     4  (25)| 00:00:01 |
|  14 |    TABLE ACCESS FULL  | EMP_TEMP |    14 |    98 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

与之对应的no_use_merge：

SQL> select  * from emp e,dept d where e.deptno=d.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |  |    14 |   812 |6   (0)| 00:00:01 |
|*  1 |  HASH JOIN   |  |    14 |   812 |6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |4 |    80 |3   (0)| 00:00:01 |
---------------------------------------------------------------------------

USE_NL:

SQL> select e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d,emp_temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.loc='CHICAGO' order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4019883924
------------------------------------------------------------------------------------------------
| Id  | Operation| Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT|       |     5 |   270 |    18 (6)| 00:00:01 |
|   1 |  SORT ORDER BY|       |     5 |   270 |    18 (6)| 00:00:01 |
|   2 |   NESTED LOOPS|       |     5 |   270 |    17 (0)| 00:00:01 |
|   3 |    NESTED LOOPS |       |     5 |   175 |    10 (0)| 00:00:01 |
|   4 |     NESTED LOOPS|       |     5 |   140 |     3 (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT       |     1 |    11 |     2 (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN| IDX_DEPT_LOC |     1 |       |     1 (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| EMP       |     5 |    85 |     1 (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN| IDX_EMP_DEPT |     5 |       |     0 (0)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL| EMP_TEMP     |     1 |     7 |     1 (0)| 00:00:01 |
|* 10 |    TABLE ACCESS FULL| JOBS       |     1 |    19 |     1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

USE_HASH:

SQL> select e.ename,j.job,e.sal,d.deptno from emp e,jobs j,dept d,emp_temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.loc='CHICAGO' order by e.ename;

6 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3131502444

-----------------------------------------------------------------------------------------------

| Id  | Operation       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |      |     5 |   270 |    12 (9)| 00:00:01 |

|   1 |  SORT ORDER BY       |      |     5 |   270 |    12 (9)| 00:00:01 |

|*  2 |   HASH JOIN       |      |     5 |   270 |    11 (0)| 00:00:01 |

|*  3 |    HASH JOIN       |      |     5 |   235 |     8 (0)| 00:00:01 |

|*  4 |     HASH JOIN       |      |    14 |   504 |     6 (0)| 00:00:01 |

|   5 |      TABLE ACCESS FULL       | EMP      |    14 |   238 |     3 (0)| 00:00:01 |

|   6 |      TABLE ACCESS FULL       | JOBS      |    14 |   266 |     3 (0)| 00:00:01 |

|   7 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |    11 |     2 (0)| 00:00:01 |

|*  8 |      INDEX RANGE SCAN       | IDX_DEPT_LOC |     1 |       |     1 (0)| 00:00:01 |

|   9 |    TABLE ACCESS FULL       | EMP_TEMP     |    14 |    98 |     3 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

加入ordered hint可以走出自己想要的执行计划