SQL Snippets ™: Tutorials for Oracle Developers

Restrictions

Materialized views with the following characteristics cannot have query rewrite enabled:

• the defining query references functions which are not DETERMINISTIC
• an expression in the defining query is not repeatable; e.g. an expression containing the USER pseudo column or the SYSTIMESTAMP function.

Attempting to violate these restrictions results in an error.

create materialized view mv
  ENABLE QUERY REWRITE
  as select key, val, USER from t
;
  as select key, val, USER from t
                      *
ERROR at line 3:
ORA-30353: expression not supported for query rewrite


 

Capabilities

A few different materialized view query rewrite capabilities exist. In EXPLAIN_MVIEW we used a utility called MY_MV_CAPABILITIES to explore a materialized view's refresh capabilities. In the snippets below we will use this same utility to explore rewrite capabilities.

First lets look at a simple, single table materialized view with query rewrite disabled.

create materialized view mv
  DISABLE QUERY REWRITE
  as select key, val from t
;

set long 5000

select my_mv_capabilities( 'MV', '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
 

This materialized view obviously has no rewrite capabilities available to it. (Descriptions of each capability name are available at Table 8-7 CAPABILITY_NAME Column Details.)

Enabling query rewrite on the materialized view changes this.

alter materialized view mv ENABLE QUERY REWRITE ;

select my_mv_capabilities( 'MV', 'REWRITE' ) as mv_report from dual ;
 

MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REWRITE

  REWRITE_FULL_TEXT_MATCH

  REWRITE_PARTIAL_TEXT_MATCH

  REWRITE_GENERAL
 

Now all rewrite capabilities are available. If the materialized view happened to referenced a remote table then some rewrite capabilities would be available, but not others.

drop materialized view mv ;

create materialized view mv
  enable query rewrite
  as select key, val from T@REMOTE
;

select my_mv_capabilities( 'MV', 'REWRITE' ) as mv_report from dual ;
 

MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REWRITE

  REWRITE_PARTIAL_TEXT_MATCH

  REWRITE_GENERAL

Not Capable of:

  REWRITE_FULL_TEXT_MATCH
    T
    mv references a remote table or view in the FROM list
 

Cleanup

drop materialized view mv ;