push_pred用法

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 |
----------------------------------------------------------------------------------------------------

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