MAT_VIEW ACCESS (BY INDEX ROWID)

Description

Performs a lookup based on an index ROWID directly against a materialized view

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;

    CREATE INDEX i1 ON mv1 (c1);

The statement

    SELECT /*+ INDEX (mv1,i1) */ sum_c2 
    FROM mv1
    WHERE c1 = 0;

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   MAT_VIEW ACCESS (BY INDEX ROWID) OF 'MV1'
2   1     INDEX (RANGE SCAN) OF 'I1'