Shulou(Shulou.com)06/01 Report--

Hint related to the table join order:

Execution sequence emp-jobs-dept

SQL > select / * + ordered * / e.enamecamera j.jobree.salre d.deptno from emp eDifft d where e.empno=j.empno and e.deptno=d.deptno and d.localizationCHICAGO 'order by e.ename 6 rows selected.Execution Plan---Plan hash value: 477715418 Murray- -| 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 size898 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 6 rows processed

The order of tables after modifying from: the order of execution is changed to emp-dept-jobs

SQL > select / * + ordered * / e.enamecamera j.jobree.salre d.deptno from emp edept d order by e.ename jobs j where e.empno=j.empno and e.deptno=d.deptno and d.localizationCHICAGO 'order by e.ename 6 rows selected.Execution Plan---Plan hash value: 3709357593 Murray- -| 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 | | 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 for multiple target tables. It means that the optimizer takes the join result of the multiple tables we specify as the driving result set in the process of joining the target sql table, and takes the first target table that appears from left to right in the leading hint as the first driver table in the whole table join process: (emp_temp--emp--dept-jobs) for those that are not specified, the optimizer can adjust The join order of the ordered table is specified dead.

SQL > select / * + leading (te) * / e.ename j.jobte e.salred.deptno from emp eDiffect jobs jdept d dept d where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d. Locale CHICAGO 'order by e.ename 6 rows selected.Execution Plan---Plan hash value: 3130730953 Murray- -| 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 |-

The target table specified in USE_MERGE: should be the driven table in the sort merge join:

SQL > select / * + use_merge (e) * / * from emp e _ dept d where e.deptno=d.deptno 14 rows selected.Execution Plan---Plan hash value: 4082513813 Murray- -| 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.jobree.salred.deptno from emp eDifferent jobs jpcct where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.localizationCHICAGO 'order by e.ename 6 rows selected.Execution Plan---Plan hash value: 2730522951 Murray- -| 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 | 23511 (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 |-|

Corresponding no_use_merge:

SQL > select / * + ordered no_use_merge (d) * / * from emp e-description d where e.deptno=d.deptno 14 rows selected.Execution Plan---Plan hash value: 1123238657 Murray- | 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 (eForce j, d, t) * / e.enamedepartment j.jobree.salred.deptno from emp eDiffect dline emptiness temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.locale CHICAGO 'order by e.ename 6 rows selected.Execution Plan---Plan hash value: 4019883924Murray- -| 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 | 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 (eForce j, d, t) * / e.enamedepartment j.jobree.salred.deptno from emp eDiffect dline emptiness temp t where e.empno=j.empno and e.deptno=d.deptno and e.ename=t.ename and d.locale 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 |

-

Joining ordered hint can get out of the execution plan you want.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.