ORACLE谓词推入初解

oracle谓词推入：所谓推入，是把库表中判断的条件推入进视图中进行判断。后续不再对库表进行筛选。

创建视图

create or replace view haoview as
select hao1.* from hao1,hao2
where hao1.object_id=hao2.object_id;
那么对于这样一个简单的查询，可见谓词hao3.object_name=haoview.object_name被merge到了view中：
select hao3.object_name
from hao3,haoview
where hao3.object_name=haoview.object_name
and hao3.object_id=999;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 129 (3)| 00:00:02 |
| 1 | NESTED LOOPS | | 1 | 44 | 129 (3)| 00:00:02 |
|* 2 | HASH JOIN | | 1 | 40 | 128 (3)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 20 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | HAO3IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | HAO1 | 36311 | 709K| 125 (2)| 00:00:02 |
|* 6 | INDEX RANGE SCAN | HAO2IDX | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HAO3"."OBJECT_NAME"="HAO1"."OBJECT_NAME")
4 - access("HAO3"."OBJECT_ID"=999)
6 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID")

接着，我把haoview放到outer join的右侧，这是haoview就属于unmergeable view了，

优化器默认无法将谓词merge进这个haoview中，于是就看到了haoview单独先执行：

select hao3.object_name
from hao3,haoview
where hao3.object_name=haoview.object_name(+)
and hao3.object_id=999;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 153 (5)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 86 | 153 (5)| 00:00:02 |