Query Rewrite Feature in Materialized Views

Query Rewrite Feature in Materialized Views:
--------------------------------------------------------------
Setup the Environment:
This includes creating a MV_CAPABILITIES_TABLE and a customised function to test this feature on Materialized views.

create table MV_CAPABILITIES_TABLE
( statement_id varchar(30) ,
mvowner varchar(30) ,
mvname varchar(30) ,
capability_name varchar(30) ,
possible character(1) ,
related_text varchar(2000) ,
related_num number ,
msgno integer ,
msgtxt varchar(2000) ,
seq number) ;

The above table can be created by executing the below script:
$ORACLE_HOME/rdbms/admin/utlxmv.sql

You can create a customised function to generate query rewrite functionalities reports on materialized views.
Refer the below URL to create a function called my_mv_capabilities.
http://www.sqlsnippets.com/en/topic-12884.html

Create/Test the Query Rewrite Feature of Materialized Views:
Create a table called dept:
SQL> create table dept (id number, name varchar2(20));
SQL> insert into dept values (1, 'Finance');
SQL> insert into dept values (2, 'Admin');
SQL> insert into dept values (3, 'HR');
SQL> commit;

Create a test mview:
SQL> Create materialized view test DISABLE QUERY REWRITE as select id,name from dept;

Generate Report:
SQl> set long 5000
SQL> select my_mv_capabilities( 'TEST', 'REWRITE' ) as mv_report from dual ;

MV_REPORT
--------------------------------------------------------------------------------
Not Capable of:
REWRITE

REWRITE_FULL_TEXT_MATCH
query rewrite is disabled on the materialized view

REWRITE_PARTIAL_TEXT_MATCH
query rewrite is disabled on the materialized view

REWRITE_GENERAL
query rewrite is disabled on the materialized view

The above report clarifies that the MV has no query rewrite feature available.

Enable Query Rewrite:
SQL>alter materialized view test enable query rewrite;

Generate Report:
SQl> set long 5000
SQL> select my_mv_capabilities( 'TEST', 'REWRITE' ) as mv_report from dual ;

MV_REPORT
--------------------------------------------------------------------------------
Capable of:
REWRITE

REWRITE_FULL_TEXT_MATCH

REWRITE_PARTIAL_TEXT_MATCH

REWRITE_GENERAL

The above report clarifies that the MV has no query rewrite feature enabled.

Effect of query rewrite on PLAN:
A very good analysis is available below:
http://www.sqlsnippets.com/en/topic-12918.html

How Query Rewrite can be enabled from database level:
Following 2 initialization parameters can enable this feature in database:
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced