• Welcome
• Projects
• Presentations
• Research

VIEW PUSHED PREDICATE

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

Example

This example was developed using Oracle 9.2.0.1 on Windows 2000

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 TRUEA

    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

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   NESTED LOOPS (OUTER)
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     VIEW PUSHED PREDICATE
4   3       NESTED LOOPS
5   4         INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)
6   4         INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)