从上面的执行计划可以看出,确实没有出现关键字“VIEW”,表EMP的扫描结果所对应的Cardinality的值确实是100,但连接顺序不是上面提到的顺序,而是先选择的表DEPT。这说明上述三个Hint中的Merge Hint和Cardinality Hint生效了,但Ordered Hint被Oracle忽略了。这是因为受到了查询转换的干扰(对内嵌视图V做视图合并是一种查询转换)。
为了证明上述SQL的Ordered Hint被Oracle忽略是因为受到了查询转换的干扰,现在将内嵌视图V中的merge替换为no_merge(不让内嵌视图做视图合并),再次执行该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 /*+ no_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: 2898000699
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 728 | 14 (22)| 00:00:01 |
| 1 | SORT ORDER BY | | 8 | 728 | 14 (22)| 00:00:01 |
|* 2 | HASH JOIN | | 8 | 728 | 13 (16)| 00:00:01 |
|* 3 | HASH JOIN | | 100 | 6500 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 100 | 4600 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0)| 00:00:01 |
| 6 | VIEW | | 5 | 130 | 6 (17)| 00:00:01 |
| 7 | HASH GROUP BY | | 5 | 185 | 6 (17)| 00:00:01 |
| 8 | MERGE JOIN | | 5 | 185 | 6 (17)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
| 10 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 11 | SORT JOIN | | 14 | 364 | 4 (25)| 00:00:01 |
| 12 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------