Oracle谓词推入:所谓推入,是把库表中判断的条件推入进视图中进行判断。后续不再对库表进行筛选。
创建视图
create table hao1 as select object_id ,object_name from dba_objects ;
create table hao2 as select object_id ,object_name from dba_objects ;
create table hao3 as select object_id ,object_name from dba_objects ;
create index indx1 on hao1(object_id ) ;
create index indx2 on hao2(object_id ) ;
create index indx3 on hao3(object_id ) ;
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 | | 5 | 855 | 128 (2)| 00:00:02 |
| 1 | NESTED LOOPS | | 5 | 855 | 128 (2)| 00:00:02 |
|* 2 | HASH JOIN | | 6 | 948 | 122 (2)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID | HAO3 | 1 | 79 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INDX3 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | HAO1 | 95655 | 7379K| 120 (2)| 00:00:02 |
|* 6 | INDEX RANGE SCAN | INDX2 | 1 | 13 | 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")
select /*+ no_merge(haoview) */ hao3.object_name
from hao3, haoview
where hao3.object_name = haoview.object_name
and hao3.object_id = 999;
接着,我把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 |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 870 | | 685 (1)| 00:00:09 |
|* 1 | HASH JOIN OUTER | | 6 | 870 | | 685 (1)| 00:00:09 |
| 2 | TABLE ACCESS BY INDEX ROWID | HAO3 | 1 | 79 | | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDX3 | 1 | | | 1 (0)| 00:00:01 |
| 4 | VIEW | HAOVIEW | 75453 | 4863K| | 683 (1)| 00:00:09 |
|* 5 | HASH JOIN | | 75453 | 6778K| 1848K| 683 (1)| 00:00:09 |
| 6 | INDEX FAST FULL SCAN | INDX2 | 75452 | 957K| | 58 (2)| 00:00:01 |
| 7 | TABLE ACCESS FULL | HAO1 | 95655 | 7379K| | 120 (2)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("HAO3"."OBJECT_NAME"="HAOVIEW"."OBJECT_NAME"(+))
3 - access("HAO3"."OBJECT_ID"=999)
5 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID")
接着,我们来使用这里的hint push_pred强制优化器将谓词merge进view中,可见到“VIEW PUSHED PREDICATE”:
select /*+push_pred(haoview)*/ 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 | | 2 | 64 | 124 (2)| 00:00:02 |
| 1 | NESTED LOOPS OUTER | | 2 | 64 | 124 (2)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDX3 | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | HAOVIEW | 1 | 2 | 122 (2)| 00:00:02 |
| 5 | NESTED LOOPS | | 2 | 70 | 122 (2)| 00:00:02 |
|* 6 | TABLE ACCESS FULL | HAO1 | 2 | 60 | 120 (2)| 00:00:02 |
|* 7 | INDEX RANGE SCAN | INDX2 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("HAO3"."OBJECT_ID"=999)
6 - filter("HAO1"."OBJECT_NAME"="HAO3"."OBJECT_NAME")
7 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID")
于是,会有同学问,那么merge hint能否有同样的效果呢?答案是,对于这种unmergeable view来说,merge hint无效。
select /*+merge(haoview)*/ hao3.object_name
from hao3,haoview
where hao3.object_name = haoview.object_name(+)
and hao3.object_id=999;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 192 | | 421 (2)| 00:00:06 |
|* 1 | HASH JOIN OUTER | | 2 | 192 | | 421 (2)| 00:00:06 |
| 2 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 30 | | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDX3 | 1 | | | 1 (0)| 00:00:01 |
| 4 | VIEW | HAOVIEW | 86350 | 5565K| | 418 (2)| 00:00:06 |
|* 5 | HASH JOIN | | 86350 | 2951K| 1440K| 418 (2)| 00:00:06 |
| 6 | INDEX FAST FULL SCAN | INDX2 | 86351 | 421K| | 54 (2)| 00:00:01 |
| 7 | TABLE ACCESS FULL | HAO1 | 86350 | 2529K| | 119 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("HAO3"."OBJECT_NAME"="HAOVIEW"."OBJECT_NAME"(+))
3 - access("HAO3"."OBJECT_ID"=999)
5 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID")
可见,对于此种身处outger join右侧的view来说,merge hint已经无能为力了。
---后续忽略
1、创建视图
SQL> create view v_p as select * from emp where deptno=10;
View created.
SQL> select * from v_p ;--视图走全表
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 3 | 117 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1109 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> select empno,dname from v_p,dept
where v_p.deptno=dept.deptno; - -此语句有谓词推入
Execution Plan
----------------------------------------------------------
Plan hash value: 568005898
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 3 | 54 | 4 (0)| 00
:00:01 |
| 1 | NESTED LOOPS (嵌套循环、效率一般不高) | | 3 | 54 | 4 (0)| 00
:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00
:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00
:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 3 | 21 | 3 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPT"."DEPTNO"=10) --筛选语句从emp.deptno=10推送至dept表的deptno。
4 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
使用hint,强制sql走hash连接(是否使用谓词推入要看具体的需求、慎用!!)
SQL> select /*+use_hash(v_p,dept)*/empno,dname from v_p,dept
2 where v_p.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2263032238
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 3 | 54 | 5 (20)| 00
:00:01 |
|* 1 | HASH JOIN | | 3 | 54 | 5 (20)| 00
:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00
:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00
:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 3 | 21 | 3 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPT"."DEPTNO")
3 - access("DEPT"."DEPTNO"=10)
4 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed