Lesson 1

The Oracle database contains a number of features designed to deliver improved performance for types of database access that have become more popular. Improvements in the cost-based optimizer, as described in the previous module, have increased the ability to use the star schemas of the data warehouse ^[1]. Materialized views are another feature designed to improve the performance of the data warehouse.

In this module, you will learn 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 Structured Query Language (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. Describe what conditions allow for query rewriting
7. Describe the function of the DBMS_OLAP and DBMS_MVIEW packages

Since Oracle8i, materialized views have provided another means of achieving a significant speedup of query performance. Summary-level information derived from a fact table and grouped along dimension values is stored as a materialized view. Queries that can use this view are directed to the view, transparently to the user and the SQL they submit. Oracle has continued to improve optimizer usage of materialized views with each new release of the database.

Materialized views were introduced for the creation of summary tables for facts and dimensions that can represent rollup levels in the hierarchies. A materialized view provides precomputed summary data; most importantly, a materialized view is automatically substituted for a larger detailed table when appropriate. The cost-based query optimizer can perform query rewrites to these summary tables and rollup levels in the hierarchy transparently, often resulting in dramatic increases in performance. For instance, if a query can be answered by summary data based on sales by month, the query optimizer will automatically substitute the materialized view for the more granular table when processing the query. A query at the quarter level might use monthly aggregates in the materialized view, selecting the months needed for the quarter(s). Oracle Database 10g added query rewrite capabilities such that the optimizer can make use of multiple appropriate materialized views.
Materialized views can be managed through Oracle Enterprise Manager. The SQL Advisor^[2] accessible in Enterprise Manager includes a SQL Access Advisor that can recommend when to create materialized views

Materialized views can also be used for other purposes, such as providing snapshot replication for distributed sites or downloading extracts in a mobile computing environment.
To improve the performance of an application, you can make local copies of remote tables that use distributed data, or create summary tables based on group by operations. Oracle provides materialized views to store copies of data or aggregations. Materialized views can be used to replicate all or part of a single table, or to replicate the result of a query against multiple tables; refreshes of the replicated data can be done automatically by the database at time intervals that you specify. In this module, you will see the general usage of materialized views, including their refresh strategies, followed by a description of the optimization strategies available. In the next lesson, we will discuss materialized views and their uses.

[1]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.

[2] SQL Advisor: Oracle Database 10g added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository or that you have specified yourself.