Materialized Views


posted Jan 17, 2011, 7:28 AM by Sachchida Ojha

DBMS_MVIEW enables you to understand capabilities for materialized views and potential materialized views, including their rewrite availability. It also enables you to refresh materialized views that are not part of the same refresh group and purge logs. DBMS_MVIEW is a synonym for DBMS_SNAPSHOT.

Summary of DBMS_MVIEW Subprograms

Subprogram Description


Performs a process to preserve materialized view data needed for refresh


Ensures that the materialized view data for the master table is valid and that the master table is in the proper state


Estimates the size of a materialized view that you might create, in bytes and rows


Explains what is possible with a materialized view or potential materialized view


Explains why a query failed to rewrite or why the optimizer chose to rewrite a query with a particular materialized view or materialized views


Returns the value of the I_AM_REFRESH package state

PMARKER Function

Returns a partition marker from a rowid, and is used for Partition Change Tracking (PCT)


Purges rows from the direct loader log after they are no longer needed by any materialized views (used with data warehousing)

PURGE_LOG Procedure

Purges rows from the materialized view log


Purges rows from the materialized view log

REFRESH Procedures

Refreshes one or more materialized views that are not members of the same refresh group


Refreshes all materialized views that do not reflect changes to their master table or master materialized view


Refreshes all table-based materialized views that depend on a specified master table or master materialized view, or list of master tables or master materialized views


Enables the administration of individual materialized views


Enables the administration of individual materialized views once invoked at a master site or master materialized view site to unregister a materialized view

Materialized view

posted Jan 17, 2011, 7:25 AM by Sachchida Ojha

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data. Materialized views, which store data based on remote tables are also, know as snapshots (in previous versions of oracle), Materialized Views can increase many times the speed of queries that access huge data records.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.

1-2 of 2