MAT_VIEW REWRITE ACCESS (FULL)

Description

Performs a full table scan against a materialized view following a query rewrite

Versions

This operation is implemented in the following versions

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 following examples assume that the QUERY_REWRITE_INTEGRITY parameter is set to the default value of ENFORCED

If the QUERY_REWRITE_ENABLED parameter is set to FALSE

    ALTER SESSION SET query_rewrite_enabled = FALSE;

the statement

    SELECT c1,SUM(c2) FROM mv1
    GROUP BY c1;

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   HASH (GROUP BY)
2   1     TABLE ACCESS (FULL) OF 'T1'

If the QUERY_REWRITE_ENABLED parameter is set to TRUE

    ALTER SESSION SET query_rewrite_enabled = TRUE;

the same statement

    SELECT c1,SUM(c2) FROM mv1
    GROUP BY c1;

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   MAT_VIEW REWRITE ACCESS (FULL) OF 'MV1'