Introduction to Read-Only Materialized Views in Oracle 19c

Read-Only Materialized views in Oracle 19c are a powerful feature that allows you to store the results of a query physically, providing significant performance benefits, especially for complex queries and data warehouse environments. Unlike regular views, which are virtual and re-execute their underlying query each time they are accessed, materialized views store the query result set in a table, allowing for faster data retrieval.

The Uses of Materialized Views

Materialized views can be used to reduce network loads, enable data subsetting, and facilitate disconnected computing. They help distribute corporate databases across regional databases, easing the load on a single server. Materialized views also allow for data subsetting, replicating only specific rows or columns of data relevant to a particular region, thus reducing unnecessary network traffic. Additionally, materialized views support disconnected computing by not requiring a continuous network connection and allowing manual or scheduled refreshes, making them suitable for use on devices like laptops.

Creating Materialized Views

Creating a materialized view in Oracle 19c involves specifying the query that the view will encapsulate, as well as several options that control how the materialized view is refreshed and maintained. Here’s a basic syntax for creating a materialized view:

CREATE MATERIALIZED VIEW mv_name
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT column1, column2, ...
FROM table_name
WHERE conditions;

Explanation:

• BUILD IMMEDIATE: The materialized view is created and populated immediately when the statement is executed.
• REFRESH FAST ON DEMAND: The materialized view is refreshed quickly (only the changes are applied) and manually, usually through a scheduled job.

CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

In this example, a materialized view named sales_summary_mv is created to store the summary of sales data, grouped by product_id.

Checking and Managing Materialized Views

To check the details of existing materialized views, you can query the USER_MVIEWS view:

SELECT mview_name, refresh_method, last_refresh_date
FROM user_mviews;

Managing Materialized Views:

Refreshing a Materialized View:

You can manually refresh a materialized view using:

EXEC DBMS_MVIEW.REFRESH('sales_summary_mv', 'F');

The 'F' denotes a fast refresh. You can use 'C' for complete refresh, which rebuilds the entire materialized view.

Listing Master Database Information For Materialized Views

-- List the existing Materialized views and theirs details.
SELECT MVIEW_NAME, 
       OWNER, 
       MASTER_LINK,
       REFRESH_METHOD, 
       LAST_REFRESH_DATE, 
       LAST_REFRESH_TYPE,
       DECODE(FAST_REFRESHABLE, 
              'NO', 'NO',
              'DML', 'YES',    
              'DIRLOAD', 'DIRECT LOAD ONLY',
              'DIRLOAD_DML', 'YES',
              'DIRLOAD_LIMITEDDML', 'LIMITED') Fast_Refresh
    FROM DBA_MVIEWS;

Dropping a Materialized View:

To drop a materialized view, use:

DROP MATERIALIZED VIEW sales_summary_mv;

Types of Materialized Views

Primary Key Materialized Views: Primary key materialized views are the default type of materialized view. These use the primary key of the base table for fast refreshes. They are the most common type.

CREATE MATERIALIZED VIEW hr.employees_mv1 WITH PRIMARY KEY
  AS SELECT * FROM hr.employees@orc1.example.com;

Object Materialized Views: An object materialized view is a type of materialized view based on an object table, created using the OF type clause. It is structured similarly to an object table, consisting of row objects, each identified by an object identifier (OID) column.

CREATE TYPE oe.category_typ AS OBJECT
   (category_name           VARCHAR2(50), 
    category_description    VARCHAR2(1000), 
    category_id             NUMBER(2));
/

CREATE TABLE oe.categories_tab OF oe.category_typ
    (category_id    PRIMARY KEY);

Rowid Materialized Views: These use the ROWID of the base table and are typically used when a table does not have a primary key.

CREATE MATERIALIZED VIEW oe.orders REFRESH WITH ROWID AS
  SELECT * FROM oe.orders@orc1.example.com;

Scheduled Refresh in Materialized Views

Scheduled refresh involves setting a refresh interval for a refresh group during its creation. The interval must be set to a future time and must be longer than the time needed to complete a refresh. Relative date expressions change based on the last refresh date, while explicit date expressions are fixed. The frequency of refresh should match your environment’s tolerance for stale data.

These examples below show how to schedule different refresh intervals for materialized views, allowing you to tailor the refresh frequency to your specific requirements.

-- This example creates a materialized view that refreshes every day at midnight.
CREATE MATERIALIZED VIEW daily_sales_summary_mv
REFRESH COMPLETE
START WITH SYSDATE
NEXT TRUNC(SYSDATE + 1) + 1/24
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

-- Create a materialized view that refreshes every hour.
CREATE MATERIALIZED VIEW hourly_sales_summary_mv
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/24
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

-- Create a materialized view that refreshes every Monday at 6 AM.
CREATE MATERIALIZED VIEW weekly_sales_summary_mv
REFRESH COMPLETE
START WITH NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 6/24
NEXT NEXT_DAY(TRUNC(SYSDATE + 7), 'MONDAY') + 6/24
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

-- Create a materialized view that refreshes on the first day of each month at 2 AM.
CREATE MATERIALIZED VIEW monthly_sales_summary_mv
REFRESH COMPLETE
START WITH TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') + 2/24
NEXT TRUNC(ADD_MONTHS(SYSDATE + 31, 1), 'MM') + 2/24
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Refresh Groups

When a refresh group is refreshed, all materialized views that are added to a particular refresh group are refreshed at the same time. This ensures transactional consistency between the related materialized views in the same refresh group.

-- Create a new refresh group
BEGIN
   DBMS_REFRESH.MAKE(
      name         => 'my_refresh_group',        -- Name of the refresh group
      list         => '',                        -- Initially, no materialized views are in the group
      next_date    => SYSDATE,                   -- When to start the refresh
      interval     => 'SYSDATE + 1/24',          -- Refresh interval (every hour in this case)
      implicit_destroy => FALSE,                 -- Do not destroy the group if empty
      lax          => FALSE                      -- Strict mode, no automatic adjustments
   );
END;
/

-- Add the materialized views to the refresh group.
BEGIN
   DBMS_REFRESH.ADD(
      name => 'my_refresh_group', 
      list => 'sales_summary_mv'
   );

   DBMS_REFRESH.ADD(
      name => 'my_refresh_group', 
      list => 'customer_summary_mv'
   );
END;
/

-- Verify the refresh group and the materialized views it contains
SELECT *
FROM user_refresh
WHERE rname = 'MY_REFRESH_GROUP';

-- List the refresh groups.
SELECT *
FROM DBA_REFRESH 
ORDER BY 1;

Fast Refresh in Materialized Views

Fast Refresh in Materialized Views is a method that allows the materialized view to be updated incrementally, rather than completely rebuilding the entire view. This means that only the changes made to the base tables (such as inserts, updates, and deletes) since the last refresh are applied to the materialized view. This approach is significantly more efficient than a complete refresh, especially when dealing with large datasets, because it reduces the amount of data that needs to be processed and transferred.

For fast refresh to work, certain conditions must be met:

• Materialized View Log: A materialized view log must be created on the base tables. This log captures the changes (DML operations) made to the table since the last refresh, which are then applied to the materialized view during a fast refresh.
• Eligible Queries: The query used to define the materialized view must be compatible with fast refresh. For instance, the query should not contain complex joins, subqueries, or certain types of aggregations that prevent fast refresh.
• Indexes: Sometimes, appropriate indexing on the base tables and the materialized view itself can improve the performance of a fast refresh.

A materialized view log is a table associated with a master table that records all DML changes made to the master table. It is essential for enabling fast refresh of a materialized view, as it tracks the changes that have occurred since the last refresh. Each master table can have only one materialized view log, regardless of how many materialized views depend on it. During a fast refresh, the materialized view applies the changes recorded in the log since the last refresh.

In the following example, the materialized view sales_summary_mv will be refreshed incrementally based on the changes recorded in the materialized view log of the sales table.

-- Create the Materialized View Log related to the source table.
CREATE MATERIALIZED VIEW LOG ON sales 
  WITH ROWID
  INCLUDING NEW VALUES;

-- The MV Log creates a table named MLOG$_<base_tablename> where changed rows are logged.
SELECT * 
FROM MLOG$_SALES;


-- Create the Materialized view
CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

-- Check the existing Materialized View Logs
SELECT DISTINCT LOG_TABLE, 
       LOG_OWNER, 
       MASTER, 
       ROWIDS, 
       PRIMARY_KEY, 
       OBJECT_ID,
       FILTER_COLUMNS 
    FROM USER_MVIEW_LOGS;
    
-- Check the Materialized Views that use the Materialized View Log
SELECT L.LOG_TABLE, L.LOG_OWNER, B.MASTER, B.MVIEW_ID, R.NAME  
FROM ALL_MVIEW_LOGS L, ALL_BASE_TABLE_MVIEWS B, ALL_REGISTERED_MVIEWS R
WHERE B.MVIEW_ID = R.MVIEW_ID
AND B.OWNER = L.LOG_OWNER
AND B.MASTER = L.MASTER;    

Fast refresh reduces the CPU and I/O resources required and minimizes the time the materialized view is unavailable during the refresh process, which is crucial for real-time reporting and data analysis.

Analyzing Materialized Views.

The EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW procedure in Oracle is used to analyze a materialized view (or a materialized view query) and provide information about whether it can be fast refreshed and what factors might prevent it from being fast refreshed. It also provides details about any restrictions or conditions that need to be met for various types of refresh methods.

When you execute DBMS_MVIEW.EXPLAIN_MVIEW('<schema>.<mv_name>'), Oracle analyzes the materialized view to determine whether it can support a fast refresh, and if not, why. The procedure writes the results of this analysis to a table called MV_CAPABILITIES_TABLE.

Before running it for the first time we need to create the required table using the following script.

sqlplus hr/hr@xepdb1 @$ORACLE_HOME/rdbms/admin/utlxmv.sql

Once the required table is created we can execute the explain plan for the materialized view.

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('HR.SALES_SUMMARY_MV');

SELECT capability_name, possible, related_text, msgtxt
FROM MV_CAPABILITIES_TABLE
WHERE mvname = 'SALES_SUMMARY_MV';

Advantages of Using Materialized Views

1. Performance Improvement: Materialized views store precomputed results, significantly reducing query execution time for complex aggregations and joins.
2. Efficient Use of Resources: By refreshing only the changes (fast refresh), materialized views reduce the overhead on the database.
3. Data Aggregation: Materialized views are ideal for summarizing and aggregating data in data warehouse environments, simplifying reporting.
4. Reduced Network Load: In distributed databases, materialized views help in reducing network load by storing copies of data locally.

Wrapping Up

Materialized views in Oracle 19c are a crucial tool for enhancing query performance and managing data efficiently, particularly in data warehouse and distributed database environments. They offer flexibility in how data is stored, refreshed, and managed, providing both performance and resource management benefits. Understanding the different types of materialized views and how to implement them effectively is key to leveraging Oracle 19c’s full potential in database optimization.

By using materialized views, you can significantly reduce query processing time and resource consumption, making them an essential feature for any Oracle DBA managing large or complex datasets.

Oracle Database Administrator’s Guide – Managing Read-Only Materialized Views