In Oracle, a Materialized View (MV) is a database object that contains the results of a query. It stores data physically and periodically refreshes to synchronize with the underlying tables, improving performance for complex queries, aggregations, and joins.

Types of Materialized Views in Oracle

Oracle provides different types of materialized views based on the refresh method and purpose:

1. Based on Refresh Method

a. Complete Refresh

b. Fast Refresh

c. Force Refresh

COMPLETE REFRESH rebuilds the MV fully each time.

FAST REFRESH only processes incremental changes (requires MV logs and strict rules).

Unsupported Query in Materialized View in case of FAST REFRESH

Certain queries prevent FAST REFRESH, including:

·       Aggregate functions without a GROUP BY

·       Complex joins or subqueries

·       DISTINCT, UNION, MINUS, INTERSECT

·       Non-deterministic functions (SYSDATE, USER, CURRENT_TIMESTAMP)

Solution: Ensure the query is simple and follows the rules for FAST REFRESH. Try using only a single-table query or a JOIN with primary keys.

Key Conditions for FAST Refresh with Aggregation:

Oracle strictly demands:

·       Include explicitly the column COUNT(*) in your aggregated MV query.

·       Materialized View logs must include all grouped and aggregated columns explicitly.

·       Aggregates allowed:
✅ COUNT, SUM, AVG, MIN, MAX

REFRESH FORCE means Oracle attempts FAST REFRESH first; if it's impossible (due to complexity or missing MV logs), Oracle will perform a COMPLETE REFRESH automatically instead.

·       It's flexible: prioritizes FAST refresh but falls back safely to COMPLETE refresh.

2. Based on Refresh Timing

a. On Demand

              Refreshed manually using DBMS_MVIEW.REFRESH.

b. On Commit

Automatically refreshed when a transaction commits on the base table.

----------------------------------------------------------------------------------------------------------------

PostgreSQL Materialized Views

Key Features:

• Only supports COMPLETE (full) refresh.
  (No built-in incremental or FAST refresh available.)
• Refresh must be triggered manually.
• PostgreSQL doesn't yet support automatic ON COMMIT refresh (built-in).
• Does not require MV logs, but no incremental built-in refresh either.

SQL Server Materialized Views (Indexed Views)

In SQL Server, materialized views are known as Indexed Views.

What is an Indexed View in SQL Server?

• SQL Server provides materialized views through Indexed Views, which physically store view results in the database.
• These indexed views provide significant performance improvements similar to Oracle's materialized views.