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 |
| 2 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HAO3IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | HAOVIEW | 36309 | 2340K| 150 (4)| 00:00:02 |
|* 5 | HASH JOIN | | 36309 | 850K| 150 (4)| 00:00:02 |
| 6 | INDEX FAST FULL SCAN | HAO2IDX | 36309 | 141K| 22 (5)| 00:00:01 |
| 7 | TABLE ACCESS FULL | HAO1 | 36311 | 709K| 125 (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 | | 1 | 40 | 128 (2)| 00:00:02 |
| 1 | NESTED LOOPS OUTER | | 1 | 40 | 128 (2)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 36 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HAO3IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | HAOVIEW | 1 | 4 | 126 (2)| 00:00:02 |
| 5 | NESTED LOOPS | | 1 | 24 | 126 (2)| 00:00:02 |
|* 6 | TABLE ACCESS FULL | HAO1 | 1 | 20 | 125 (2)| 00:00:02 |
|* 7 | INDEX RANGE SCAN | HAO2IDX | 1 | 4 | 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 | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 153 (5)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 86 | 153 (5)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HAO3IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | HAOVIEW | 36309 | 2340K| 150 (4)| 00:00:02 |
|* 5 | HASH JOIN | | 36309 | 850K| 150 (4)| 00:00:02 |
| 6 | INDEX FAST FULL SCAN | HAO2IDX | 36309 | 141K| 22 (5)| 00:00:01 |
| 7 | TABLE ACCESS FULL | HAO1 | 36311 | 709K| 125 (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")

可见,对于此种身处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
  2    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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值