Materialized Views

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