MAT_VIEW ACCESS (SAMPLE BY ROWID RANGE)

Description

Performs a full table sample scan directly against a materialized view using range of ROWIDs

Versions

This operation is implemented in the following versions

10.1.0
10.2.0

Example

This example was developed using Oracle 10.2.0.1 on Linux

This example requires the following object definitions

    CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);

    CREATE MATERIALIZED VIEW mv1
    BUILD IMMEDIATE
    ENABLE QUERY REWRITE
    AS
      SELECT c1,SUM(c2) AS sum_c2 
      FROM t1
      GROUP BY c1;

The statement

    SELECT c1,sum_c2 FROM mv1 SAMPLE (10)
    WHERE ROWID > 
    (
      SELECT MIN (ROWID) FROM mv1
    );

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   MAT_VIEW ACCESS (SAMPLE BY ROWID RANGE) OF 'MV1'
2   1      SORT (AGGREGATE)
3   2        MAT_VIEW ACCESS (FULL) OF 'MV1'