How are materialized views managed?

How are materialized views managed?

Rating:

Materialized views can be created either through the CREATE MATERIALIZED VIEW statement or the Oracle Enterprise Manager (OEM). The CREATE MATERIALIZED VIEW statement can also be used for populating the materialized views. A materialized view can be derived from an existing user-defined materialized view. In this case, the ON PREBUILT TABLE clause of the CREATE MATERIALIZED VIEW is used. However, if a user-defined materialized view does not exist, a materialized view can be populated by using either the BUILD IMMEDIATE or BUILD DEFERRED clause of the CREATE MATERIALIZED VIEW statement, depending upon whether the materialized view has to be populated immediately or at some later time, respectively. However, the BUILD DEFERRED materialized view cannot be used until a refresh is performed on it. As soon as the first refresh is performed on the BUILD DEFERRED materialized view, it is enabled, provided the ENABLE QUERY REWRITE clause is specified.

Materialized views are used for query rewrites in expensive data warehouse environments, where the queries are complicated and involve multiple joins. However, materialized views cannot use the query rewrite feature by themselves unless the QUERY_REWRITE_ENABLED initialization parameter is set to TRUE and the ENABLE QUERY REWRITE clause is specified at the time of creation of the materialized view that has to be made available for query rewrites. If the above clause was not specified, or the DISABLE QUERY REWRITE clause was used instead at the time of creation of the materialized view, then the query rewrite feature for the materialized view can be enabled using the ALTER MATERIALIZED VIEW statement.

Materialized views are refreshed to synchronize the data in the master site of the materialized view and the materialized view itself. Materialized views can be refreshed one at a time or in bulk by using refresh groups. Materialized views can be refreshed either on demand (the ON DEMAND mode of refreshing) or each time a transaction that brought some changes in the base tables of the materialized view, commits (the ON COMMIT mode of refreshing). The ON DEMAND mode has two modes under it - the manual complete mode and the manual force mode. The manual complete mode refreshes the underlying materialized view by recalculating the defining query of the associated materialized view. Manual force mode attempts to perform a fast refresh of the materialized view. If it cannot perform a fast refresh, it does a complete refresh instead. A user can also specify which type of refresh is to be performed on the materialized view. The following refresh options are available:

• Complete
• Fast
• Force
• Never

Complete - In this case, the materialized view is refreshed by executing the defining query of the materialized view. This replaces the data in the materialized view by the result set of the query. If a complete refresh is performed on the master materialized view, then a complete refresh should be performed on each materialized view associated with the above master view. If this is not done, then Oracle returns an error.

Fast - In this case, the materialized view logs, a SQL*Loader direct path, or a partition maintenance operation are used to update the rows that have changed since the last refresh operation. Hence, this refreshing option is incremental rather than total.

Force - In this case, the first attempt to refresh a materialized view is made through a fast refresh. However, if this refresh option fails, then a complete refresh is performed on the materialized view.

Never - In this case, the specified materialized view is never refreshed by Oracle.

Rating: