与表连接顺序的相关hint：
执行顺序emp-jobs-dept
SQL> select /*+ ordered */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 /*+ ordered */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 /*+ leading(t e) */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 /*+ use_merge(e) */ * 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 /*+ use_merge(e j d t) */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 /*+ ordered no_use_merge(d) */ * 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 /*+ use_nl(e,j, d, t) */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 /*+ ordered use_hash(e,j, d, t) */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可以走出自己想要的执行计划
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
