ITPub博客

首页 > Linux操作系统 > Linux操作系统 > push_pred用法

push_pred用法

原创 Linux操作系统 作者:wei-xh 时间:2011-08-01 11:10:48 0 删除 编辑

create table hao1 as select * from dba_objects;
create table hao2 as select * from dba_objects;
create table hao3 as select * from dba_objects;
create index hao3idx on hao3(object_id);
create index hao2idx on hao2(object_id);
create or replace view haoview as
select hao1.* from hao1,hao2
where hao1.object_id=hao2.object_id;

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 |    90 |    79   (3)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |         |     1 |    90 |    79   (3)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| HAO3    |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | HAO3IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                       | HAOVIEW | 20093 |  1295K|    76   (2)| 00:00:01 |
|*  5 |    HASH JOIN                 |         | 20093 |   569K|    76   (2)| 00:00:01 |
|   6 |     INDEX FAST FULL SCAN     | HAO2IND | 20157 |    98K|    11   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL        | HAO1    | 20156 |   472K|    64   (0)| 00:00:01 |

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 |    47 |    67   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |         |     1 |    47 |    67   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| HAO3    |     1 |    43 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | HAO3IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE      | HAOVIEW |     1 |     4 |    65   (0)| 00:00:01 |
|   5 |    NESTED LOOPS              |         |     1 |    29 |    65   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL        | HAO1    |     1 |    24 |    64   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN         | HAO2IND |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

create index hao1_oi on hao1(object_name);
select /*+ gather_plan_statistics push_pred(haoview)*/ hao3.object_name
from hao3,haoview
where hao3.object_name=haoview.object_name(+)
and hao3.object_id>999 and hao3.object_id<1008;
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |      8 |00:00:00.01 |      27 |
|   1 |  NESTED LOOPS OUTER            |         |      1 |      1 |      8 |00:00:00.01 |      27 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | HAO3    |      1 |      1 |      8 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN            | HAO3IDX |      1 |      1 |      8 |00:00:00.01 |       3 |
|   4 |   VIEW PUSHED PREDICATE        | HAOVIEW |      8 |      1 |      8 |00:00:00.01 |      22 |
|   5 |    NESTED LOOPS                |         |      8 |      1 |      8 |00:00:00.01 |      22 |
|   6 |     TABLE ACCESS BY INDEX ROWID| HAO1    |      8 |      1 |      8 |00:00:00.01 |      13 |
|*  7 |      INDEX RANGE SCAN          | HAO1_OI |      8 |      1 |      8 |00:00:00.01 |      11 |
|*  8 |     INDEX RANGE SCAN           | HAO2IND |      8 |      1 |      8 |00:00:00.01 |       9 |
----------------------------------------------------------------------------------------------------

无论如何,对于可以merge的视图,有时候ORACLE也会不对其进行MERGE。执行计划里也可能会出现view pushed predicate。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-703655/,如需转载,请注明出处,否则将追究法律责任。

上一篇: no_merge的含义
下一篇: disable or 扩展
请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315300