Oracle Materialized View

Materialized views in Oracle are database objects that store the results of a query physically. They are useful for improving query performance, particularly for complex aggregations and data summarization, as they allow you to query precomputed results rather than re-executing a complex query each time.

Features of Materialized Views in Oracle:

Physical Storage: Stores actual data, unlike regular views.
Performance Boost: Speeds up complex query performance by using precomputed results.
Refresh Capability: Can be refreshed manually or automatically (complete, fast, or force refresh).
Periodic Updates: Allows data to be updated on a schedule, ensuring data consistency while not needing real-time accuracy.
Query Optimization: Can be used to optimize and pre-aggregate data for fast access.
Replication: Supports data replication in distributed database systems for better data availability.

Build Options:

BUILD IMMEDIATE: The materialized view is populated with data as soon as it is created.

BUILD DEFERRED: The structure of the materialized view is created without populating it. The data is loaded later, either manually or through a refresh operation.

Refresh Mechanisms:

Fast Refresh: Updates only the changed rows using a materialized view log.( If materialized view log is not present refresh was faild)

Complete Refresh: truncate the Materialized View and Rebuilds the entire view.

Force Refresh: Chooses between a fast or complete refresh automatically based on availability.

Auto Refresh Methods:

ON COMMIT: Automatically refreshes when a transaction on the base tables is committed.

ON DEMAND: The view is refreshed manually or Scheduled task.

Sample Table and data:

CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
product_id NUMBER,
quantity NUMBER,
amount NUMBER,
sale_date DATE
);

INSERT INTO sales (sale_id, product_id, quantity, amount, sale_date)VALUES (1, 101, 5, 100, TO_DATE('2024-11-01', 'YYYY-MM-DD'));
INSERT INTO sales (sale_id, product_id, quantity, amount, sale_date)VALUES (2, 102, 3, 75, TO_DATE('2024-11-02', 'YYYY-MM-DD'));
INSERT INTO sales (sale_id, product_id, quantity, amount, sale_date)VALUES (3, 103, 8, 200, TO_DATE('2024-11-03', 'YYYY-MM-DD'));
INSERT INTO sales (sale_id, product_id, quantity, amount, sale_date)VALUES (4, 101, 2, 40, TO_DATE('2024-11-04', 'YYYY-MM-DD'));
INSERT INTO sales (sale_id, product_id, quantity, amount, sale_date)VALUES (5, 102, 7, 150, TO_DATE('2024-11-05', 'YYYY-MM-DD'));

Materialized View with ON COMMIT:

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

Materialized View with ON DEMAND:

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

Manual Refresh Command:

exec  DBMS_MVIEW.REFRESH('sales_summary_mv', 'F'); — 'F' for fast refresh, 'C' for complete refresh

(OR)

execute DBMS_MVIEW.REFRESH( LIST => 'sales_summary_mv', METHOD => 'FORCE' );

TIMING/SCHEDULED Refresh:

Options:

START WITH SYSDATE : The initial refresh occurs at the time.
NEXT SYSDATE + 1/24 : Subsequent refreshes occur every hour (1/24 represents one hour).
NEXT SYSDATE + 7 : Every 7 days once refresh.
NEXT SYSDATE + 30/(24*60) : Every 30 Mins Once Refresh

CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH force 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;

Materialized View with refresh Fast:

FAST REFRESH Is Not Supported:

Complex Queries: Queries with DISTINCT, UNION, MINUS, Sub Query etc.

No Materialized View Log: Logs are required on base tables.

Unsupported Functions: Use of unsupported functions or aggregations.

Missing Keys: Logs must include PRIMARY KEY, ROWID, or relevant columns.

PRIMARY KEY: when the table has a primary key.

ROWID: when the table does not have a primary key.

SEQUENCE: Use for scenarios that require tracking the order of changes.

Materialized View with refresh Fast (PRIMARY KEY):

Materialized View Log:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Materialized View:

CREATE MATERIALIZED VIEW sales_summary_mv_pk
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;

Materialized View with refresh Fast (ROWID):

Materialized View Log:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID
INCLUDING NEW VALUES;

Materialized View:

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

Materialized View with refresh Fast (SEQUENCE):

Materialized View Log:

CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE (product_id, quantity, amount) INCLUDING NEW VALUES;

Materialized View:

CREATE MATERIALIZED VIEW sales_summary_mv_seq
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;

Query Rewrite in Oracle:

Query Rewrite is a feature in Oracle that allows the optimizer to rewrite queries to take advantage of materialized views (or other precomputed results). When query rewrite is enabled, Oracle can automatically rewrite queries that refer to base tables in a way that uses precomputed data in materialized views instead. This improves performance by avoiding real-time computation and using the materialized view data instead.

1. Enabling Query Rewrite:

You can enable query rewrite at the database or materialized view level.

Enable Query Rewrite for the Entire Database:

ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE;

2. Enable Query Rewrite for a Materialized View:

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

SELECT * FROM v$mq_query_rewrite WHERE object_name = 'SALES';

SELECT mview_name FROM user_mviews;

Altering a Materialized View:

Enabling Query Rewrite:

ALTER MATERIALIZED VIEW sales_summary_mv ENABLE QUERY REWRITE;

Disabling Query Rewrite:

ALTER MATERIALIZED VIEW sales_summary_mv DISABLE QUERY REWRITE;

Changing Refresh Method:

ALTER MATERIALIZED VIEW sales_summary_mv REFRESH COMPLETE ON DEMAND;

Changing Storage Parameters:

ALTER MATERIALIZED VIEW sales_summary_mv STORAGE (BUFFER_POOL DEFAULT);

Dropping a Materialized View:

DROP MATERIALIZED VIEW sales_summary_mv;

DROP MATERIALIZED VIEW sales_summary_mv CASCADE CONSTRAINTS;    -- Drop a materialized view and its associated log

STORAGE OPTIONS:

        materialized view storage refers to how the data for a materialized view is physically stored in the database. This includes options for managing how much space is allocated, how data is cached, and how it is maintained for performance.

You can control various aspects of storage for materialized views using the STORAGE clause in the CREATE MATERIALIZED VIEW statement or by altering the storage parameters later. The storage options help manage the physical storage of the materialized view data, especially in terms of space, memory, and how it's cached.

Basic Storage Parameters:

BUFFER_POOL:

Determines which buffer pool the materialized view will use. Oracle has different buffer pools like DEFAULT, KEEP, and RECYCLE. These define how data is cached in memory.

DEFAULT: Standard buffer pool.

KEEP: For frequently accessed data.

RECYCLE: For less frequently used data.

PCTFREE:

Specifies the percentage of each data block to leave free for updates. This is important for maintaining free space for row updates.

PCTUSED:

Specifies the percentage of a data block that must be used before new rows can be added to that block.

INITIAL:

Specifies the initial amount of space allocated for the materialized view when it is created. This is typically used to allocate space for the first block of the table.

NEXT:

Specifies the next extent size after the initial extent is used. This controls the growth of the materialized view.

MAXEXTENTS:

Controls the maximum number of extents that can be allocated for the materialized view. Extents are units of space allocation in the database.

FREELIST:

Determines the number of freelists, which are used to manage space for inserted rows.

Example:

CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
STORAGE (
INITIAL 10M
NEXT 5M
MAXEXTENTS 50
PCTFREE 10
PCTUSED 40
FREELISTS 3
BUFFER_POOL KEEP
)
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(amount) AS total_amount FROM sales GROUP BY product_id;

Alter Storage Parameters for an Existing Materialized View:

ALTER MATERIALIZED VIEW sales_summary_mv STORAGE (PCTFREE 20, PCTUSED 50);