Performance Tuning   «Prev  Next»

Lesson 4 Create materialized views for summary data and joins
Objective Write SQL to create a materialized view that stores summary data.

Create materialized views for summary Data

As mentioned above, a materialized view can represent aggregate values from one or more base tables, the values from joined tables, or both. For data warehousing applications, materialized views usually include some aggregate values.

Steps in the Creation Process

Following are the steps required to create a materialized view:
  1. Identify the candidate: The first step in creating a materialized view for summary data is to identify aggregate values that are frequently used in queries. In the sample database, the SALES_TOTAL column in the SALE_HEADER table is frequently used for analysis, so creating a materialized view of this based on the month the sale was made might be an appropriate candidate for a materialized view.
  2. Decide on options: Once you identify data for a candidate materialized view, you should decide which options are appropriate for the characteristics of the view. For the candidate materialized view, the following options would be appropriate:
  1. Build_clause: You will want this view to be immediately available. Because the BUILD IMMEDIATE option is the default, you will not have to specify this clause.
  2. Refresh_clause: Because this is not really time-critical data, performing the refresh on demand is sufficient for this materialized view.
  3. Refresh_type: It makes sense to designate the refresh type as FORCE, because a fast refresh is preferable, if it can be done, but a refresh should be done on demand even if a fast refresh cannot be done.
  4. ENABLE/DISABLE QUERY REWRITE: Because the purpose of this materialized view is to improve query performance, you should use the ENABLE QUERY REWRITE clause.
  5. SELECT_statement: The SELECT statement for this view would choose the summary of SALE_TOTAL based on the month the sale was made, as seen below: 


SELECT TO_CHAR(SALES_DATE,'YY-MONTH') SALES_MONTH, 
SUM(SALE_TOTAL) TOTAL_SALES, COUNT(*)
FROM SALE_HEADER
GROUP BY TO_CHAR(SALES_DATE,'YY-MONTH')

  1. Create the view: The final step is to create the materialized view by using the following syntax:

CREATE MATERIALIZED VIEW MONTHLY_SALES_MV 
REFRESH FORCE ON DEMAND 
ENABLE QUERY REWRITE AS 
SELECT TO_CHAR(SALES_DATE,'YY-MONTH') SALES_MONTH, 
SUM(SALE_TOTAL) TOTAL_SALES, COUNT(*) 
FROM SALE_HEADER 
GROUP BY TO_CHAR(SALES_DATE,'YY-MONTH')

For a materialized view containing aggregate data from a single table, you must always include COUNT(*) as a part of the query.

Materialized Views for Joins

You can also create a materialized view to reduce the overhead of creating joins between tables. Of course, materialized views can also contain both aggregate data and data from joined tables. The steps used to create a materialized view for joined data are exactly the same as those used to create a materialized view for aggregate data. The only difference is that the SQL statement used to create the view includes more than one table.
If you want to have a fast refresh for a materialized view containing only joins, you must have an index defined on the ROWID [1] column of all the tables used in the view. In the next lesson, you will learn how to create a materialized view for data from joined tables.

Create Materialized View - Exercise

Click the Exercise link below to practice creating a materialized view for aggregate data.
Create Materialized View - Exercise

[1]ROWID: An internal value that uniquely identifies each individual row in an Oracle database.

SEMrush Software