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
Here’s an example of SQL to create a **materialized view** that stores summary data in Oracle 12c:
Use Case: Assume you have a `sales` table and want to create a materialized view summarizing total sales by product and year.
CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
product_id,
EXTRACT(YEAR FROM sales_date) AS sales_year,
SUM(sales_amount) AS total_sales
FROM
sales
GROUP BY
product_id, EXTRACT(YEAR FROM sales_date);
Explanation:
-
CREATE MATERIALIZED VIEW sales_summary_mv:
- Creates a materialized view named
sales_summary_mv
.
-
BUILD IMMEDIATE:
- Specifies that the materialized view is populated immediately after creation.
-
REFRESH FAST ON COMMIT:
- The materialized view will use fast refresh mode (incremental updates if possible).
- It refreshes automatically whenever a transaction commits changes to the base table.
-
AS SELECT:
- Defines the SQL query that fetches data for the materialized view. Here, it calculates the total sales grouped by
product_id
and year.
-
GROUP BY:
- Ensures the summary data is aggregated as required.
Prerequisites for FAST Refresh:
-
Materialized View Log:
- Ensure the base table (
sales
) has a materialized view log.
- Create it like this:
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (sales_date, product_id, sales_amount)
INCLUDING NEW VALUES;
-
Key Constraint:
- The base table should have a primary key or unique constraint.
-
Data Types:
- Columns used in the materialized view query should allow for efficient row changes to be tracked.
This setup allows Oracle to efficiently manage the materialized view and keep it up-to-date with minimal overhead.
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:
- 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.
- 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:
- 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.
- Refresh_clause: Because this is not really time-critical data, performing the refresh on demand is sufficient for this materialized view.
- 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.
-
ENABLE/DISABLE QUERY REWRITE:
Because the purpose of this materialized view is to improve query performance, you should use the ENABLE QUERY REWRITE
clause.
- 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')
- 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
[1]ROWID: An internal value that uniquely identifies each individual row in an Oracle database.