scott@TEST>select /*+ use_hash(t1) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno>t2.deptno and t2.loc='CHICAGO';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 37 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 11 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 26 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
从上面的执行计划中看出use_hash确实是被Oracle忽略了，这是因为哈希连接只适用于等值连接条件，不等值的连接条件对哈希连接而言是没有意义的，所以上述Hint就被Oracle忽略了。
3 使用的Hint自相矛盾
如果使用的组合Hint是自相矛盾的，则这些自相矛盾的Hint都会被Oracle忽略。但Oracle只会将自相矛盾的Hint全部忽略掉，但如果使用的组合Hint中还有其他有效的Hint，则这些有效Hint不受影响。
看一个使用自相矛盾Hint的实例，先执行单个Hint的SQL
scott@TEST>select /*+ index_ffs(dept pk_dept)*/ deptno from dept;
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2578398298
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 | 2 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_DEPT | 4 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
......
scott@TEST>select /*+ full(dept)*/ deptno from dept;
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
从上面的输出可以看出单独使用上面的两个Hint都能被Oracle生效，但如果这两个Hint合并到一起使用就不是那么回事了：
scott@TEST>select /*+ index_ffs(dept pk_dept) full(dept)*/ deptno from dept;
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2913917002
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
从上面的输出可以看出执行计划没有走Hint中指定的执行计划，而是对主键索引PK_DEPT做的是INDEX FULL SCAN这说明Hint中的两个都失效了。
再来看下面的例子：