push_pred

The PUSH_PRED hint instructs the optimizer to push a join predicate into the view.

push_pred只能使用nested loop的方式来驱动VIEW PUSHED PREDICATE的部分完成sql的执行.

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1 as select * from dba_objects where object_id < 20000;
create table t2 as select * from dba_objects where object_id < 5000;
create table t3 as select * from dba_objects where object_id < 15000;
create index t1_object_id on t1(object_id);
create index t2_object_id on t2(object_id);
create index t3_object_id on t3(object_id);

create or replace view v1 as select t1.* from t1,t2 where t1.object_id=t2.object_id;

select /*+ gather_plan_statistics no_merge(v1) push_pred(v1) */
t3.object_name, t3.owner, t3.object_id
from t3, v1
where t3.object_name = v1.object_name(+)
and t3.object_id = 14;

–no hint

SQL> select /*+ gather_plan_statistics*/
  2   t3.object_name, t3.owner, t3.object_id
  3    from t3, v1
  4   where t3.object_name = v1.object_name(+)
  5     and t3.object_id = 14;

Execution Plan
----------------------------------------------------------
Plan hash value: 3506806881

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    96 |    82   (2)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |              |     1 |    96 |    82   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T3           |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T3_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                       | V1           |  4997 |   322K|    80   (2)| 00:00:01 |
|*  5 |    HASH JOIN                 |              |  4997 |   151K|    80   (2)| 00:00:01 |
|   6 |     INDEX FAST FULL SCAN     | T2_OBJECT_ID |  4997 | 19988 |     5   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL        | T1           | 19707 |   519K|    74   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."OBJECT_NAME"="V1"."OBJECT_NAME"(+))
   3 - access("T3"."OBJECT_ID"=14)
   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

–add hint

SQL> select /*+ gather_plan_statistics push_pred(v1) */
  2   t3.object_name, t3.owner, t3.object_id
  3    from t3, v1
  4   where t3.object_name = v1.object_name(+)
  5     and t3.object_id = 14;

Execution Plan
----------------------------------------------------------
Plan hash value: 2971835043

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    32 |    77   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |              |     1 |    32 |    77   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T3           |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T3_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE      | V1           |     1 |     2 |    75   (0)| 00:00:01 |
|   5 |    NESTED LOOPS              |              |     1 |    31 |    75   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL        | T1           |     1 |    27 |    74   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN         | T2_OBJECT_ID |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T3"."OBJECT_ID"=14)
   6 - filter("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

jonathen lewis’s websit have a very good artical descripting the push_pred hint.

 

 

此条目发表在Hint, Uncategorized分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s