scott@TEST>select /*+ index_ffs(dept pk_dept) full(dept) cardinality(dept 1000) */ deptno from dept;
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2913917002
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 3000 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_DEPT | 1000 | 3000 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
从上面的输出可以看出执行计划走的仍然是对主键索引PK_DEPT做的是INDEX FULL SCAN，但是做INDEX FULL SCAN反回结果集的cardinality从原来的4变为了1000，说明cardinality(dept 1000)生效了，也验证了如果使用的组合Hint中还有其他有效的Hint，则这些有效Hint不受影响。
4 使用的Hint受到了查询转换的干扰
有时候，查询转换也会导致相关的Hint失效，即Hint被Oracle忽略还可能是因为受到了查询转换的干扰。
下面来看一个因为使用了查询转换而导致相关Hint被Oracle忽略掉的实例。
创建一个测试表jobs
scott@TEST>create table jobs as select empno,job from emp;
Table created.
构造一个SQL
select /*+ ordered cardinality(e 100) */
e.ename, j.job, e.sal, v.avg_sal
from emp e,
jobs j,
(select /*+ merge */
e.deptno, avg(e.sal) avg_sal
from emp e, dept d
where d.loc = 'chicago'
and d.deptno = e.deptno
group by e.deptno) v
where e.empno = j.empno
and e.deptno = v.deptno
and e.sal > v.avg_sal
order by e.ename;
上面的SQL是两个表(EMP和JOBS)和内嵌视图V关联的SQL，其中内嵌视图V又是由表EMP和DEPT关联后得到的。在此SQL中使用了三个Hint，其中merge用于让内嵌视图V做视图合并，ordered表示上述SQL在执行时表EMP、JOBS和内嵌视图V的连接顺序应该和它们在该SQL的SQL文本中出现的顺序一致，即它们应该是按照从左至右的顺序依次做表连接。
如果上述三个Hint都生效的话，那目标SQL的执行计划中应该不会出现关键字“VIEW”(表示做了视图合并，体现了Merge Hint的作用)，表EMP、JOBS和内嵌视图V的连接应该会变成表EMP、JOBS和内嵌视图V所对应的基表EMP和DEPT的连接，且连接的先后顺序应该是EMP->JOBS->内嵌视图V所对应的基表EMP和DEPT(体现了Ordered Hint的作用)，外围查询中表EMP的扫描结果所对应的Cardinality的值应该是100(体现了Cardinality Hint的作用)。
现在看一下实际情况，执行上面的SQL:
1 scott@TEST>select /*+ ordered cardinality(e 100) */
2 e.ename, j.job, e.sal, v.avg_sal
3 from emp e,
4 jobs j,
5 (select /*+ merge */
6 e.deptno, avg(e.sal) avg_sal
7 from emp e, dept d
8 where d.loc = 'chicago'
9 and d.deptno = e.deptno
10 group by e.deptno) v
11 where e.empno = j.empno
12 and e.deptno = v.deptno
13 and e.sal > v.avg_sal
14 order by e.ename;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 930847561
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 156 | 19656 | 15 (20)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY | | 156 | 19656 | 15 (20)| 00:00:01 |
|* 3 | HASH JOIN | | 156 | 19656 | 14 (15)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 467 | 53705 | 10 (10)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 100 | 8900 | 7 (15)| 00:00:01 |
| 8 | TABLE ACCESS FULL| EMP | 100 | 5800 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL| JOBS | 14 | 434 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------