一个详细的关于VIEW PUSHED PREDICATE的例子（带filter））
DescriptionPush 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
SELECTt1.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
页:
[1]