--不使用Hint
scott@TEST>select e.ename,j.job,e.sal,d.deptno
2 from emp e,jobs j,dept d,emp_temp t
3 where e.empno=j.empno
4 and e.deptno=d.deptno
5 and d.loc='CHICAGO'
6 and e.ename=t.ename
7 order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 558051962
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 270 | 12 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 270 | 12 (17)| 00:00:01 |
|* 2 | HASH JOIN | | 5 | 270 | 11 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 5 | 235 | 8 (13)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 5 | 140 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_EMP_DEPT | 5 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 85 | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | EMP_TEMP | 14 | 98 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
......
--使用LEADING Hint
scott@TEST>select /*+ leading(t e) */ e.ename,j.job,e.sal,d.deptno
2 from emp e,jobs j,dept d,emp_temp t
3 where e.empno=j.empno
4 and e.deptno=d.deptno
5 and d.loc='CHICAGO'
6 and e.ename=t.ename
7 order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 937897748
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 270 | 15 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 270 | 15 (20)| 00:00:01 |
|* 2 | HASH JOIN | | 5 | 270 | 14 (15)| 00:00:01 |
|* 3 | HASH JOIN | | 5 | 175 | 10 (10)| 00:00:01 |
|* 4 | HASH JOIN | | 14 | 336 | 7 (15)| 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 FULL | DEPT | 1 | 11 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
......
--使用Ordered Hint
scott@TEST>select /*+ ordered */ e.ename,j.job,e.sal,d.deptno
2 from emp e,jobs j,dept d,emp_temp t
3 where e.empno=j.empno
4 and e.deptno=d.deptno
5 and d.loc='CHICAGO'
6 and e.ename=t.ename
7 order by e.ename;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2459794491
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 270 | 14 (22)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 270 | 14 (22)| 00:00:01 |
|* 2 | HASH JOIN | | 5 | 270 | 13 (16)| 00:00:01 |
|* 3 | HASH JOIN | | 5 | 235 | 10 (20)| 00:00:01 |
| 4 | MERGE JOIN | | 14 | 504 | 6 (17)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 238 | 2 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 7 | SORT JOIN | | 14 | 266 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | EMP_TEMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
......