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:

1. CREATE MATERIALIZED VIEW sales_summary_mv:
   • Creates a materialized view named sales_summary_mv.
2. BUILD IMMEDIATE:
   • Specifies that the materialized view is populated immediately after creation.
3. 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.
4. 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.
5. GROUP BY:
   • Ensures the summary data is aggregated as required.

Prerequisites for FAST Refresh:

1. 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;
       

2. Key Constraint:
   • The base table should have a primary key or unique constraint.
3. 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.

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')

3. 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.

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.

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.