Managing Materialized Views in Oracle 23c

Materialized views (MVs) are a core performance feature in Oracle 23c. They store the results of complex queries so that frequently executed reports, dashboards, and analytic workloads can be satisfied without re-reading and aggregating the base tables every time. In this lesson we focus on the modern toolset for managing MVs: DBMS_MVIEW for refresh and analysis, DBMS_STATS for accurate statistics, SQL Access Advisor for recommendations, and the Query Rewrite engine and Automatic Materialized Views for transparent performance gains.

1. Key components in the 23c materialized view toolset

• DBMS_MVIEW – Primary PL/SQL package for creating, refreshing, validating, and analyzing materialized views.
• DBMS_STATS – Collects optimizer statistics on base tables and materialized views; completely replaces the old ANALYZE command.
• SQL Access Advisor (DBMS_ADVISOR) – Analyzes a SQL workload and recommends indexes, partitions, and materialized views.
• Query Rewrite Engine – Automatically rewrites user queries to use materialized views when it is safe and beneficial to do so.
• Automatic Materialized Views (AutoMV) – In Oracle 23c/23ai, the database can automatically identify and maintain candidate materialized views based on workload patterns.

2. Managing materialized views with DBMS_MVIEW

The DBMS_MVIEW package is the main interface for day-to-day management of materialized views in Oracle 23c. It replaces older terminology such as “snapshots” and is the preferred synonym for DBMS_SNAPSHOT.

2.1 Creating a materialized view for query rewrite

To benefit from query rewrite, define the materialized view with ENABLE QUERY REWRITE and a refresh policy that matches your business requirements:

CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT   s.sales_id,
         s.sales_date,
         s.customer_id,
         s.product_id,
         SUM(s.amount) AS total_amount
FROM     sales s
GROUP BY s.sales_id, s.sales_date, s.customer_id, s.product_id;

This definition allows Oracle to refresh sales_mv when changes are committed to the sales table and to rewrite compatible queries automatically.

2.2 Refreshing materialized views with DBMS_MVIEW.REFRESH

The REFRESH procedure gives fine-grained control over how and when MVs are refreshed. You can call it directly or schedule it with DBMS_SCHEDULER.

BEGIN
  DBMS_MVIEW.REFRESH(
    list           => 'SALES_MV',
    method         => 'F',       -- F = Fast, C = Complete, ? = Force
    atomic_refresh => TRUE       -- Single transaction; set to FALSE for large refreshes
  );
END;

Use atomic_refresh => FALSE for very large materialized views when you want Oracle to perform the refresh in multiple transactions to reduce undo and redo pressure.

3. Statistics management with DBMS_STATS

Accurate optimizer statistics are essential for correct query rewrite decisions. In Oracle 23c, you should always use DBMS_STATS; the ANALYZE command is retained only for specialized non-optimizer operations.

Typical steps:

• Gather stats on base tables that feed your materialized views.
• Gather stats on the materialized views themselves.
• Let automatic statistics gathering complement your manual tasks.

BEGIN
  -- Gather stats on base table
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SH',
    tabname          => 'SALES',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
  );

  -- Gather stats on materialized view
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SH',
    tabname          => 'SALES_MV',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
  );
END;

4. Using SQL Access Advisor to recommend materialized views

SQL Access Advisor evaluates a workload and recommends access structures, including materialized views. Recommendations are stored in Advisor repository views and can be scripted into DDL.

A simplified workflow is:

1. Create an Advisor task with DBMS_ADVISOR.CREATE_TASK.
2. Provide a workload: SQL Tuning Set, captured SQL, or manually supplied statements.
3. Execute the task with DBMS_ADVISOR.EXECUTE_TASK.
4. Query Advisor views to see recommended materialized views.

DECLARE
  l_task_name  VARCHAR2(30) := 'MV_RECOMMEND_TASK';
BEGIN
  DBMS_ADVISOR.CREATE_TASK(
    advisor_name => 'SQL Access Advisor',
    task_name    => l_task_name);

  -- In practice, attach a SQL Tuning Set or workload here

  DBMS_ADVISOR.EXECUTE_TASK(task_name => l_task_name);
END;

5. Query rewrite and automatic materialized views

Once materialized views exist and statistics are up to date, the Query Rewrite engine can transparently redirect user queries to use materialized views. To enable this behavior, you must:

• Define the materialized view with ENABLE QUERY REWRITE.
• Ensure the initialization parameter QUERY_REWRITE_ENABLED is set to TRUE (or at least COST) at the system or session level.
• Choose an appropriate QUERY_REWRITE_INTEGRITY level for your application’s tolerance for stale data.

ALTER SYSTEM SET query_rewrite_enabled   = TRUE;
ALTER SYSTEM SET query_rewrite_integrity = TRUSTED;

In Oracle 23c/23ai, Automatic Materialized Views build on these foundations. The database can identify frequently used query shapes, propose internal materialized views, and maintain them automatically. From the DBA’s perspective, the management patterns you have learned in this lesson still apply: keep statistics fresh, monitor the workload, and validate that query rewrite is occurring as expected.

6. Monitoring and troubleshooting materialized views

Use data dictionary views to check the status, staleness, and refresh history of your materialized views.

SELECT mview_name,
       last_refresh_type,
       last_refresh_date,
       staleness,
       compile_state
FROM   user_mviews
ORDER  BY mview_name;

7. Legacy note: DBMS_OLAP and older advisors

Earlier Oracle releases exposed materialized view advisory functions through DBMS_OLAP and views such as SYSTEM.MVIEW_EVALUATIONS. For new development in Oracle 23c and 23ai, these features are considered legacy. You should use DBMS_STATS for statistics, DBMS_MVIEW for refresh and validation, and SQL Access Advisor plus Automatic Materialized Views for strategy recommendations and workload-driven optimization.

In summary, effective management of materialized views in Oracle 23c combines good refresh design, reliable statistics, workload-aware recommendations, and careful monitoring. When these components are aligned, the Query Rewrite engine can deliver significant performance improvements without requiring changes to application SQL.

The next lesson is the module conclusion.