Performs a full table scan against a materialized view following a query rewrite
This operation is implemented in the following versions
|
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'