UNION-ALL (PARTITION)

Description

Perform a union all set operation between two or more tables within a partitioned view. Duplicate rows are not removed.

Only applicable to partitioned views

Versions

Although Oracle Corporation has stated that partitioned views will be de-supported, they are still implemented in Oracle 10.2.

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 table and view definitions

At least one of the tables in the partitioned view must be analyzed

    CREATE TABLE t1 
    (
      c1 NUMBER,
      c2 NUMBER,
      CONSTRAINT con1 CHECK (c1 = 1)
    );

    CREATE TABLE t2 
    (
      c1 NUMBER,
      c2 NUMBER,
      CONSTRAINT con2 CHECK (c1 = 2)
    );

    ANALYZE TABLE t1 COMPUTE STATISTICS;

    CREATE VIEW v1 AS
    SELECT * FROM t1
    UNION ALL
    SELECT * FROM t2;

The statement

    SELECT c1 FROM v1;

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   VIEW OF 'V1'
2   1     UNION-ALL (PARTITION)
3   2       TABLE ACCESS (FULL) OF 'T1'
4   2       TABLE ACCESS (FULL) OF 'T2'