查询转换方式有很多种:
1、视图合并( view merge ) 2、子查询展开( subquery unnesting ) 3、谓词推入 ( predicate pushdown )
1、视图合并:就是视图的基表和外部表做连接
创建一个视图
create view v1 as
select * from emp where deptno = 10;
select *
from v1, dept
where v1.deptno = dept.deptno
and v1.job = 'CLERK';
找到v1的基表emp,做了视图合并.emp和dept返回的记录很少,所以走嵌套循环和hash都很正常
不让他进行视图合并的方法:
《1》/*+ no_meger(view)*/
《2》查询块包含分析函数或聚合函数、集合运算(例如union、intersect、minus),
order by字句或者使用了rownum,视图合并将会被禁止或者限制。
rownum也会阻止谓词推入
2、(1)、走子查询展开:就是where子句后边有in,not in,exists,not exists,<,<=,>,>=等,CBO认为对SQL进行等价改写以后能够更好的进行优化,等价改写的过程就是子查询展开
SQL> select ename from emp where deptno in (select deptno from dept where dname like 'S');
执行计划
----------------------------------------------------------
Plan hash value: 330698451
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 5 | 110 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 2 (0)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------