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:
- Describe a materialized view and its uses
- Describe the parameters and what they mean in the
CREATE MATERIALIZED VIEW command
- Write SQL to create a materialized view that stores summary data
- Describe the conditions and types of indexing for materialized views
- Check the status of a materialized view and fix it
-
Descibe what conditions allow for query rewriting
- 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:
- Materialized view logging control
- Online redefinition for tables with materialized view logs
- Query rewrite during refresh
- Partition Change Tracking (PCT) refresh for union all mviews
- New and enhanced materialized view catalog views
- 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.
Glossary
The following terms were defined in this module:
- 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.
- 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.
- 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