Materialized Views

DBA's Home Page - A website for Oracle, Greenplum and PostgreSQL DBA's‎ > ‎

DBMS_MVIEW

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
BEGIN_TABLE_REORGANIZATION Procedure	Performs a process to preserve materialized view data needed for refresh
END_TABLE_REORGANIZATION Procedure	Ensures that the materialized view data for the master table is valid and that the master table is in the proper state
ESTIMATE_MVIEW_SIZE Procedure	Estimates the size of a materialized view that you might create, in bytes and rows
EXPLAIN_MVIEW Procedure	Explains what is possible with a materialized view or potential materialized view
EXPLAIN_REWRITE Procedure	Explains why a query failed to rewrite or why the optimizer chose to rewrite a query with a particular materialized view or materialized views
I_AM_A_REFRESH Function	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)
PURGE_DIRECT_LOAD_LOG Procedure	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
PURGE_MVIEW_FROM_LOG Procedure	Purges rows from the materialized view log
REFRESH Procedures	Refreshes one or more materialized views that are not members of the same refresh group
REFRESH_ALL_MVIEWS Procedure	Refreshes all materialized views that do not reflect changes to their master table or master materialized view
REFRESH_DEPENDENT Procedures	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
REGISTER_MVIEW Procedure	Enables the administration of individual materialized views
UNREGISTER_MVIEW Procedure	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