溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
與表連接順序的相關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進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
