Materialized Views

MATERIALIZED VIEW



A Materialized View Precomputes the data or executes the Query behind the view and has its results precomputed. This is very different from the normal Oracle Views which executes the Query on run time. Since the Query results are already precomputed, Accessing Data from the Materialized View is much faster when compared to Oracle Views. Materialized Views favors the creation of indexes on the precomputed data which is not possible in the case of Oracle Views which uses the indexes on the base tables on which the Oracle View is created.

The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.ENABLE Clause Specify ENABLE to enable the materialized view for query rewrite.

Enabling of query rewrite is subject to the following restrictions:

• You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC.
• You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudo columns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change).

Restrictions on the Defining Query of a Materialized View The materialized view query is subject to the following restrictions:

• The defining query of a materialized view can select from tables, views, or materialized views owned by the user SYS, but you cannot enable QUERY REWRITE on such a materialized view.
• You cannot define a materialized view with a sub query in the select list of the defining query. You can, however, include sub queries elsewhere in the defining query, such as in the WHERE clause.
• Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.
• Materialized views cannot contain columns of data type LONG.
• You cannot create a materialized view log on a temporary table. Therefore, if the defining query references a temporary table, then this materialized view will not be eligible for FAST refresh, nor can you specify the QUERY REWRITE clause in this statement.
• If the FROM clause of the defining query references another materialized view, then you must always refresh the materialized view referenced in the defining query before refreshing the materialized view you are creating in this statement.

If you are creating a materialized view enabled for query rewrite, then:

• The defining query cannot contain, either directly or through a view, references to ROWNUM, USER, SYSDATE, remote tables, sequences, or PL/SQL functions that write or read database or package state.
• Neither the materialized view nor the master tables of the materialized view can be remote.

QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY


QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database making use of the materialized views  and function based indexes while choosing the best optimized path.To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.

There are three acceptable values for query_rewrite_integrity:

enforced (default) - Presents materialized view with fresh data
trusted - Assumes that the materialized view is current
stale_tolerated - Presents materialized view with both stale and fresh data