Performance Tuning   «Prev  Next»

Lesson 9

Oracle Materialized Views Conclusion

Materialized views are a truly powerful feature of Oracle. Although materialized views are applicable only to certain usage scenarios, they can provide a real performance boost for specific situations without having to change any of the applications that are relying on your Oracle database.
In this module, you learned how to:
  1. Describe a materialized view and its uses
  2. Describe the parameters and what they mean in the CREATE MATERIALIZED VIEW command
  3. Write SQL to create a materialized view that stores summary data
  4. Describe the conditions and types of indexing for materialized views
  5. Check the status of a materialized view and fix it
  6. Descibe what conditions allow for query rewriting
  7. Describe the function of the DBMS_OLAP and DBMS_MVIEW packages

Materialized Views and Query Rewrite

Oracle Database 11g introduces new and enhanced features associated with materialized views and query rewrite. In this section we will discuss the following:
  1. Materialized view logging control
  2. Online redefinition for tables with materialized view logs
  3. Query rewrite during refresh
  4. Partition Change Tracking (PCT) refresh for union all mviews
  5. New and enhanced materialized view catalog views
  6. Query rewrite enhancements

Online Redefinition for Tables with Materialized View Logs

Oracle Database 11g now supports online redefinition of tables that have materialized view logs. You now just clone the materialized view log onto the interim table during the redefinition process as you do triggers, indexes, and so on. One requirement is that at the end of the redefinition process, you will need to perform a complete refresh of your materialized views.

If you are using Oracle for a data warehouse or remote or distributed applications, materialized views may give you a performance gain with a minimum of effort.


The following terms were defined in this module:
  1. Bitmapped Index: Uses individual bits in an index entry to identify the value of an entry. A bitmapped index works best when there are a limited number of values for an index.
  2. Data Warehouse: A type of database that is used for analysis of data. The data warehouse frequently has a different type of structure a star schema as opposed to a standard normalized schema and is also frequently separate from an organization's transaction database.
  3. ROWID: An internal value that uniquely identifies each individual row in an Oracle database.
In the next module, you will learn about index enhancements in Oracle.

Materialized Views - Quiz

Click the Quiz link below to test your understanding of the concepts presented in this module.
Materialized Views - Quiz