The Secrets of Materialized Views

Materialized views are a data warehousing/decision support system tool that can increase by many orders of magnitude the speed of queries that access a large number of records. In basic terms, they allow a user to query potentially terabytes of detail data in seconds. They accomplish this by transparently using pre-computed summarizations and joins of data. These pre-computed summaries would typically be very small compared to the original source data.

• Setup of Materialized Views

There is one mandatory INIT.ORA parameter necessary for materialized views to function, this is the COMPATIBLE parameter. The value of COMPATIBLE should be set to 8.1.0, or above, in order for query rewrites to be functional. If this value is not set appropriately, query rewrite will not be invoked.
There are two other relevant parameters that may be set at either the system-level via the INIT.ORA file, or the session-level via the ALTER SESSION command.
QUERY_REWRITE_ENABLED
Unless the value of this parameter is set to TRUE, query rewrites will not take place. The default value is FALSE.
QUERY REWRITE INTEGRITY
This parameter controls how Oracle rewrites queries and maybe set to one of three values:
ENFORCED - Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle. There are mechanisms by which we can tell Oracle about other inferred relationships, and this would allow for more queries to be rewritten, but since Oracle does not enforce those relationships, it would not make use of these facts at this level.
TRUSTED - Queries will be rewritten using the constraints that are enforced by Oracle, as well as any relationships existing in the data that we have told Oracle about, but are not enforced by the database.
STALE TOLERATED - Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is ' stale ' (out-of-sync with the details). This might be useful in an environment where the summary tables are refreshed on a recurring basis, not on commit, and a slightly out-of-sync answer is acceptable.
The needed privileges are as follows:
CREATE SESSION
CREATE TABLE
CREATE MATERIALIZED VIEW
QUERY REWRITE
Finally, you must be using the Cost Based Optimizer CBO in order to make use of query rewrite. If you do not use the CBO, query rewrite will not take place.