p; 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 |
--------------------------------------------------------------------------------------------
......
从上面的执行计划可以看出不使用Hint时表扫描顺序是DEPT->EMP->JOBS->EMP_TEMP；使用LEADING Hint时表扫描顺序是EMP_TEMP->EMP->DEPT->JOBS，EMP_TEMP做首个驱动表和表EMP的连接结果做为驱动结果集，与Hint要求一致。；使用Ordered Hint时表扫描顺序是EMP->JOBS->DEPT->EMP_TEMP，与SQL中顺序一致。
参考《基于Oracle的SQL优化》
官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF005