IMMEDIATE Refresh

•   IMMEDIATE Refresh is also called FAST Refresh.

•   It updates a materialized view (MV) only with the changes made to the base tables.

•   Materialized view logs are used to track what has changed.

•   Only the new changes since the last refresh are applied.

•   The refresh can happen automatically at commit (ON COMMIT) or manually when needed.

 

-- 1. Base Table Creation

CREATE TABLE sales_mvi (

sale_id    NUMBER PRIMARY KEY,

product_id NUMBER,

sale_date  DATE,

amount     NUMBER

);

 

-- 2. Materialized View Log Creation

-- Required for FAST refresh

CREATE MATERIALIZED VIEW LOG ON sales_mvi

WITH ROWID, SEQUENCE (product_id, sale_date, amount)

INCLUDING NEW VALUES;

 

-- 3. Materialized View (FAST REFRESH ON COMMIT)

CREATE MATERIALIZED VIEW sales_mv

REFRESH FAST ON COMMIT

AS

SELECT product_id, SUM(amount) AS total_amount

FROM sales_mvi

GROUP BY product_id;

 

-- 4. Materialized View (FAST REFRESH on Schedule)

-- Example: Hourly Refresh

CREATE MATERIALIZED VIEW sales_mv

REFRESH FAST

START WITH SYSDATE

NEXT SYSDATE + 1/24

AS

SELECT product_id, SUM(amount) AS total_amount

FROM sales_mvi

GROUP BY product_id;

 

-- 5. Sample Data Insert

INSERT INTO sales_mvi VALUES (1, 101, SYSDATE, 500);

COMMIT;

 

-- 6. Query Materialized View

SELECT * FROM sales_mv;

 

Expected Output:

PRODUCT_ID | TOTAL_AMOUNT

101        | 500