一个详细的关于VIEW PUSHED PREDICATE的例子（带filter））

oraunix

Description

Push a predicate into a view

Requires the session parameter "_push_join_predicate" to be set to TRUE

Versions

This operation was introduced in Oracle 8.1.5

This operation is implemented in the following versions

8.1.5
8.1.6
8.1.7
9.0.1
9.2.0
10.1.0
10.2.0
Example

This example was developed using Oracle 10.2.0.1 on Linux as 4

This example requires the following object definitions

CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);

CREATE TABLE t2 (c1 NUMBER);

CREATE TABLE t3 (c1 NUMBER);

CREATE INDEX i1 ON t2 (c1);

CREATE INDEX i2 ON t3 (c1);
The objects do not need to be analysed

The session parameter "_push_join_predicate" must be set to TRUE

ALTER SESSION SET "_push_join_predicate" = TRUE;
The statement

SELECT /*+ PUSH_PRED (v1) */ t1.c1,v1.c1
FROM t1,
(
   SELECT t2.c1
   FROM t2,t3
   WHERE t2.c1 = t3.c1
) v1
WHERE t1.c1 = v1.c1(+)
AND t1.c2 = 0;
generates the following execution plan

Execution Plan
----------------------------------------------------------
Plan hash value: 1633242866

--------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |    |    1 | 54 |    4 (0)| 00:00:01 |
| 1 |  NESTED LOOPS OUTER    |    |    1 | 54 |    4 (0)| 00:00:01 |
|*  2 | TABLE ACCESS FULL    | T1 |    1 | 39 |    2 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE |    |    1 | 15 |    2 (0)| 00:00:01 |
| 4 | NESTED LOOPS       |    |    1 | 39 |    2 (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN | I1 |    1 | 26 |    1 (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN | I2 |    1 | 13 |    1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

2 - filter("T1"."C2"=0)
5 - access("T2"."C1"="T1"."C1")
6 - access("T3"."C1"="T1"."C1")
   filter("T2"."C1"="T3"."C1")

Note
-----
- dynamic sampling used for this statement

ALTER SESSION SET "_push_join_predicate" =false;

The statement

SELECT  t1.c1,v1.c1
FROM t1,
(
   SELECT t2.c1
   FROM t2,t3
   WHERE t2.c1 = t3.c1
) v1
WHERE t1.c1 = v1.c1(+)
AND t1.c2 = 0;
generates the following execution plan

Execution Plan
----------------------------------------------------------
Plan hash value: 366875960

-----------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    1 | 39 |    6  (17)| 00:00:01 |
|*  1 |  HASH JOIN OUTER    |    |    1 | 39 |    6  (17)| 00:00:01 |
|*  2 | TABLE ACCESS FULL  | T1 |    1 | 26 |    2 (0)| 00:00:01 |
| 3 | VIEW             |    |    1 | 13 |    3 (0)| 00:00:01 |
| 4 | NESTED LOOPS    |    |    1 | 26 |    3 (0)| 00:00:01 |
| 5 |    TABLE ACCESS FULL| T2 |    1 | 13 |    2 (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN | I2 |    1 | 13 |    1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

1 - access("T1"."C1"="V1"."C1"(+))
2 - filter("T1"."C2"=0)
6 - access("T2"."C1"="T3"."C1")

Note
-----
- dynamic sampling used for this statement

图文精华

一个详细的关于VIEW PUSHED PREDICATE的例子（带filter））