Need for Materialized Views

You can use materialized views to increase the speed of queries on very large
databases. Queries to large databases often involve joins between tables, aggregations such as SUM, or both. These operations are expensive in terms of time and processing power. The type of materialized view you create determines how the materialized view is refreshed and used by query rewrite.

Materialized views improve query performance by precalculating expensive join and
aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response time.

Following Figure illustrates how query rewrite works.



When using query rewrite, create materialized views that satisfy the largest number of queries. For example, if you identify 20 queries that are commonly applied to the
detail or fact tables, then you might be able to satisfy them with five or six well-written materialized views. A materialized view definition can include any number of
aggregations (SUM, COUNT(x), COUNT(*), COUNT(DISTINCT x), AVG, VARIANCE,
STDDEV, MIN, and MAX). It can also include any number of joins. If you are unsure of
which materialized views to create, Oracle provides the SQL Access Advisor, which is
a set of advisory procedures in the DBMS_ADVISOR package to help in designing and
evaluating materialized views for query rewrite.

If a materialized view is to be used by query rewrite, it must be stored in the same
database as the detail tables on which it relies. A materialized view can be partitioned, and you can define a materialized view on a partitioned table. You can also define one or more indexes on the materialized view.
Unlike indexes, materialized views can be accessed directly using a SELECT statement.
However, it is recommended that you try to avoid writing SQL statements that
directly reference the materialized view, because then it is difficult to change them
without affecting the application. Instead, let query rewrite transparently rewrite your query to use the materialized view.
Note that the techniques shown in this chapter illustrate how to use materialized
views in data warehouses. Materialized views can also be used by Oracle Replication.


Components of Summary Management

Summary management consists of:
■ Mechanisms to define materialized views and dimensions.
■ A refresh mechanism to ensure that all materialized views contain the latest data.
■ A query rewrite capability to transparently rewrite a query to use a materialized
view.
■ The SQL Access Advisor, which recommends materialized views and indexes to
■ TUNE_MVIEW, which shows you how to make your materialized view fast
refreshable and use general query rewrite.
The use of summary management features imposes no schema restrictions, and can
enable some existing DSS database applications to improve performance without the
need to redesign the database or the application.

Following Figure illustrates the use of summary management in the warehousing cycle. After the data has been transformed, staged, and loaded into the detail data in the
warehouse, you can invoke the summary management process. First, use the SQL
Access Advisor to plan how you will use materialized views. Then, create materialized
views and design how queries will be rewritten. If you are having problems trying to
get your materialized views to work then use TUNE_MVIEW to obtain an optimized
materialized view create.




Understanding the summary management process during the earliest stages of data
warehouse design can yield large dividends later in the form of higher performance,
lower summary administration costs, and reduced storage requirements.

Posted in: Materialized Views