scott@TEST>select /*+ full(dept) parallel(dept 2) */ deptno from dept;
Execution Plan
----------------------------------------------------------
Plan hash value: 587379989
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 16 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000 | 13000 | 16 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1000 | 13000 | 16 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| DEPT | 1000 | 13000 | 16 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
......
从上面输出内容可以看出，现在是对表DEPT做的并行全表扫描，说明组合Hint中的两个都生效了，这个Hint的含义是既要全表扫描又要并行访问表DEPT，两者不矛盾，因为全表扫描可以并行执行。再看如下的SQL：
scott@TEST>select /*+ index(dept pk_dept) parallel(dept 2) */ deptno from dept;
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2913917002
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 26 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_DEPT | 1000 | 13000 | 26 (0)| 00:00:01 |
----------------------------------------------------------------------------
......
现在SQL走的是对索引PK_DEPT的索引全扫描，但是串行的，说明Hint中的parallel(dept 2)失效了，因为表DEPT上的主键索引PK_DEPT不是分区索引，而对于非分区索引而言，索引范围扫描或索引全扫描并不能并行执行，所以上述组合Hint中忽略了parallel(dept 2)。
再看一个HASH JOIN的例子：
下面的SQL中use_hash的Hint是生效的：
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';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 185 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 5 | 185 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 11 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
-
但是如果把SQL修改为如下则use_hash的Hint就会被忽略