Managing Materialized Views using DBMS_OLAP and DBMS_MVIEW

In Oracle 19c, managing materialized views involves understanding two different but occasionally overlapping packages:
📌 1. `DBMS_MVIEW` – Primary package for managing materialized views
This package provides procedures for:

• Refreshing materialized views
• Monitoring and optimizing performance
• Rewriting queries using materialized views
• Scheduling refreshes

✅ Commonly Used `DBMS_MVIEW` Procedures:

Procedure	Description
REFRESH	Refreshes one or more materialized views.
REFRESH_ALL_MVIEWS	Refreshes all materialized views in the database.
EXPLAIN_MVIEW	Analyzes if a materialized view is fast refreshable.
EXPLAIN_REWRITE	Explains if query rewrite will use a materialized view.
PURGE_LOG	Cleans up the materialized view refresh logs.

🔧 Example: Refreshing a Materialized View

BEGIN
  DBMS_MVIEW.REFRESH(
    list => 'SALES_MV',
    method => 'C',         -- C = Complete, F = Fast, ? = Force
    atomic_refresh => TRUE
  );
END;

📌 2. `DBMS_OLAP` – For OLAP Cubes and MV Integration
This package is used to manage OLAP metadata, dimensions, and cubes. In Oracle 19c, OLAP-based materialized views are automatically managed and stored as cube-organized materialized views.
> ⚠️ Use `DBMS_OLAP` only when you are working with Oracle OLAP (Online Analytical Processing). Otherwise, stick with `DBMS_MVIEW`.
⚙️ Typical Use of `DBMS_OLAP`:

• Refreshing cube materialized views
• Controlling cube metadata
• Integrating with materialized view refresh for query rewrite

🔧 Example: Refreshing an OLAP Cube

EXEC DBMS_OLAP.REFRESH('SALES_CUBE');

> `SALES_CUBE` must be a valid OLAP cube registered in the OLAP catalog.
🧠 Best Practices for Managing Materialized Views:

Task	Recommended Action
Schedule refresh	Use DBMS_SCHEDULER or DBMS_MVIEW.REFRESH with job queues.
Enable query rewrite	Use ENABLE QUERY REWRITE in the MV definition.
Optimize fast refresh	Ensure primary key and materialized view logs exist on base tables.
Analyze rewrite capability	Use DBMS_MVIEW.EXPLAIN_REWRITE and DBMS_MVIEW.EXPLAIN_MVIEW.
Use OLAP cubes	Use DBMS_OLAP to manage them if cubes are involved.

🧪 Checking Materialized View Status

SELECT mview_name, last_refresh_type, last_refresh_date, compile_state
FROM user_mviews;

Oracle includes two PL/SQL packages that help you to manage materialized views. The two packages are DBMS_OLAP and DBMS_MVIEW.

• The DBMS_OLAP package
  The DBMS_OLAP package is primarily used for gathering statistics that are used for data warehouses. Two specific procedures in the DBMS_OLAP procedure relate to materialized views:
  1. RECOMMEND_MV: The RECOMMEND_MV package analyzes the statistics of the uniqueness of your tables and columns and recommends which materialized views should be created. These recommendations are based on a set of all possible queries against the tables.
  2. RECOMMEND_MV_W: The RECOMMEND_MV_W procedure performs the same sort of analysis as the RECOMMEND_MV procedure, except that this procedure uses the actual workload of your database in its calculations.
• With Oracle Database 10g, the DBMS_OLAP package has been replaced with improved technology. While Oracle recommends you not begin development using DBMS_OLAP, Oracle continues to support DBMS_OLAP, and your existing applications using DBMS_OLAP will continue to work. If you are developing new or substantially modified applications and had previously used the Summary Advisor in DBMS_OLAP, you should now use the SQL Access Advisor.
• Overview:
  The DBMS_OLAP package, presented here for reasons of backward compatibility, provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program. Some of the functions generate output tables. DBMS_OLAP performs seven major functions, which include materialized view strategy recommendation, materialized view strategy evaluation, reporting and script generation, repository management, workload management, filter management, and dimension validation. To perform materialized view strategy recommendation and evaluation functions, the workload information can either be provided by the user or synthesized by the Advisor engine. In the former case, cardinality information of all tables and materialized views referenced in the workload are required. In the latter case, dimension objects must be present and cardinality information for all dimension tables, fact tables, and materialized views are required. Cardinality information should be gathered with the DBMS_STATS.GATHER_TABLE_STATS procedure. Once these functions are completed, the analysis results can be presented with the reporting and script generation function. The workload management function handles three types of workload, which are user-specified workload, SQL cache workload, and Oracle Trace workload. To process the user-specified workload, a user-defined workload table must be present in the user's schema. To process Oracle Trace workload, the Oracle Trace formatter must be run to preprocess collected workload statistics into default V-tables in the user's schema.

Several views are created when using DBMS_OLAP. All are in the SYSTEM schema. To access these views, you must have a DBA role.
SYSTEM.MVIEW_EVALUATIONS

Column	NULL?	Datatype	Description
RUNID	NOT NULL	NUMBER	Run ID identifying a unique Advisor call.
MVIEW_OWNER	-	VARCHAR2(30)	Owner of materialized view.
MVIEW_NAME	-	VARCHAR2(30)	Name of an exiting materialized view in this database.
RANK	NOT NULL	NUMBER	Rank of this materialized view in descending order of BENEFIT_TO_CSOT_RATIO.
STORAGE_IN_BYTES	-	NUMBER	Size of the materialized view in bytes.
FREQUENCY	-	NUMBER	Number of times this materialized view appears in the workload.
CUMULATIVE_BENEFIT	-	NUMBER	The cumulative benefit of the materialized view.
BENEFIT_TO_COST_RATIO	NOT NULL	NUMBER	The ratio of CUMULATIVE_BENEFIT to STORAGE_IN_BYTES.

The DBMS_MVIEW package
DBMS_MVIEW is a synonym for the DBMS_SNAPSHOT package. The package contains a number of procedures that mainly apply to the use of snapshots of a database and two that can be useful with materialized views:

1. REFRESH_ALL_MVIEWS: The REFRESH_ALL_MVIEWS procedure refreshes all the materialized views that are registered. This procedure refreshes materialized views only where the data in the base tables has been changed because the materialized view was refreshed.
2. REFRESH_DEPENDENT: The REFRESH_DEPENDENT view is used to refresh all materialized views that are dependent on a particular table. The same conditions for refreshing materialized views apply to this procedure as do to the REFRESH_ALL_MVIEWS procedure.

The following series of imagesdemonstrates the use of some of these functions: