The Recomputation Problem#

Imagine you have a dashboard that shows the "Total Sales by Store for the Year." The underlying SQL query aggregates 2 billion rows from the TRAN_DATA table.

Every time a user opens the dashboard, the database performs a massive SUM() and GROUP BY operation, burning massive CPU and taking 45 seconds to load.

A Standard View won't help, because a standard view is just a macro for a SQL query—it still executes against the base tables every time.

A Materialized View (MView) solves this. It runs the query once, and actually creates a physical table on disk to store the results. When the user queries the MView, it returns instantly because the aggregation is already done!

Creating a Materialized View#

Creating an MView is as simple as defining a query and a refresh schedule.

CREATE MATERIALIZED VIEW mv_sales_by_store
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT 
  store_id, 
  SUM(amount) as total_sales, 
  COUNT(*) as transaction_count
FROM sales
GROUP BY store_id;

• BUILD IMMEDIATE: Populates the view right now.
• REFRESH COMPLETE: When refreshed, it deletes all data and re-runs the full 2-billion-row query.
• ON DEMAND: Only refreshes when a DBA explicitly calls DBMS_MVIEW.REFRESH.

Refresh Strategies (Complete vs Fast)#

A Complete refresh of a massive table is agonizingly slow. If only 500 new sales occurred today, why recalculate the billions of historical sales?

Fast Refresh to the rescue.

A Fast Refresh uses a Materialized View Log on the base table. This log acts like a trigger, recording exactly which rows were inserted, updated, or deleted since the last refresh. When you trigger the refresh, Oracle only processes the delta (the 500 new sales) and adjusts the aggregates (e.g., adding to the SUM() and COUNT()).

Step 1: Create the MView Log

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

Step 2: Create the Fast Refresh MView

CREATE MATERIALIZED VIEW mv_sales_by_store
BUILD IMMEDIATE
REFRESH FAST ON COMMIT -- Refreshes automatically every time a sale is committed!
AS
SELECT 
  store_id, 
  SUM(amount) as total_sales, 
  COUNT(*) as transaction_count
FROM sales
GROUP BY store_id;

The Magic of Query Rewrite#

You have built mv_sales_by_store. But your BI tool (like Tableau) doesn't know about it. The BI tool is hardcoded to query the base sales table. Do you have to rewrite all your reports?

No! Enable Query Rewrite.

ALTER MATERIALIZED VIEW mv_sales_by_store ENABLE QUERY REWRITE;

When a user executes:

SELECT store_id, SUM(amount) FROM sales GROUP BY store_id;

The Cost-Based Optimizer intercepts the query. It realizes, "Wait, this aggregation perfectly matches mv_sales_by_store!" It secretly rewrites the user's query behind the scenes to select from the MView instead. The BI tool gets the result instantly without changing a single line of its code.

Common Gotchas#

Key Takeaways#