Performs a lookup based on an index ROWID directly against a materialized view
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; CREATE INDEX i1 ON mv1 (c1);
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'