Materialized Views

A Materialized View is effectively a database table that contains the results of a query. The power of materialized views comes from the fact that, once created, Oracle can automatically synchronize a materialized view's data with its source information as required with little or no programming effort.

Materialized views can be used for many purposes, including:

  • Denormalization
  • Validation
  • Data Warehousing
  • Replication.

This tutorial explores materialized view basics. After completing it you should have enough information to use materialized views effectively in simple applications. For more complex applications links at the end of the tutorial will point to information on advanced features not covered here, e.g. partitioning, refresh groups, updatable materialized views.

Terminology

With relational views the FROM clause objects a view is based on are called "base tables". With materialized views, on the other hand, these objects are either called "detail tables" (in data warehousing documentation) or "master tables" (in replication documentation and the Oracle Database SQL Reference guide). Since SQL Snippets is concerned mainly with relational uses of materialized views we will avoid the contradictory terms "master" and "detail" all together and instead use the term "base tables", thus remaining consistent with relational view terminology.

Materialized Views were originally known as "Snapshots" in early releases of Oracle. This keyword is supported for backward compatibility, but should not be used in new code. You may still see this term in some Oracle 11g materialized view error messages.

See Also

Depending on your requirements Oracle's Change Data Capture feature may meet your needs better than Materialized Views.



Reprinted from Doodles: Out of Sync with permission.





Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12868.html]SQL Snippets: SQL Features Tutorials - Materialized Views[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12868.html">SQL Snippets: SQL Features Tutorials - Materialized Views</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: SQL Features Tutorials - Materialized Views
  • URL (href): http://www.sqlsnippets.com/en/topic-12868.html