UNION ALL PUSHED PREDICATE

Description

Performs a union all set operation between two tables with a pushed predicate. Duplicate rows are not removed

Versions

This operation has been observed in the following versions

Example

This example was developed using Oracle 10.2.0.3 on Windows XP (Oracle XE)

Thanks to James Padfield for reporting this operation

This example requires the following object definitions

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);

    CREATE UNIQUE INDEX t1_pk ON t1 (c1);

    ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1);

The table does not need to be analysed

The statement

    SELECT b.c1, b.c2
    FROM
    (
      SELECT c1
      FROM t1
      WHERE ROWNUM = 1
    ) a,
    (
      SELECT *
      FROM t1
      UNION ALL
      SELECT *
      FROM t1
    ) b
    WHERE b.c1 = a.c1;

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   NESTED LOOPS
2   1     VIEW
3   2       COUNT (STOPKEY)
4   3         TABLE ACCESS (FULL) OF 'T1'
5   1     VIEW
6   5       UNION ALL (PUSHED PREDICATE)
7   6         TABLE ACCESS (BY INDEX ROWID) OF 'T1'
8   7           INDEX (UNIQUE SCAN) OF 'T1_PK'
9   6         TABLE ACCESS (BY INDEX ROWID) OF 'T1'
10  9           INDEX (UNIQUE SCAN) OF 'T1_PK'