Oracle Materialized View Logs FAQs with Examples

A materialized view log (MV log) is a database object created on a base table to record changes made to that table.

MV logs are especially important for FAST refreshable materialized views because Oracle can use the recorded changes instead of rereading the entire base table.

The key idea is:

Base Table
↓
Materialized View Log
↓
Records relevant changes
↓
FAST refresh
↓
Materialized View updated

1. What is a Materialized View Log?

A materialized view log is a table-like Oracle object that stores information about changes to a master/base table.

Example:

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

Suppose:

INSERT INTO sales
VALUES (1, 101, 500);

UPDATE sales
SET amount = 600
WHERE sale_id = 1;

DELETE FROM sales
WHERE sale_id = 1;

Oracle can record information about these changes in the MV log.

That information can later be used for FAST refresh.

2. Why do we need Materialized View Logs?

Without a suitable MV log, Oracle may have to examine much more data to determine what changed.

With an MV log:

Base table
↓
Only changes are recorded
↓
FAST refresh
↓
Use changes to update MV

This is especially useful when:

Base table = millions/billions of rows
Changes    = relatively small

Instead of recalculating everything, Oracle can process the changes.

3. Is an MV log itself a Materialized View?

No.

These are different objects.

Materialized View

Stores the result of a query.

CREATE MATERIALIZED VIEW mv_sales
AS
SELECT product_id,
SUM(amount)
FROM sales
GROUP BY product_id;

Materialized View Log

Stores change information about the base table.

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE;

Remember:

MV      = stores query result

MV Log  = stores information about base-table changes

4. On which table is the MV log created?

You create the MV log on the base/master table, not on the materialized view.

SALES
↓
MV LOG ON SALES
↓
MV_SALES

SQL:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE;

Not:

CREATE MATERIALIZED VIEW LOG ON mv_sales;

5. What is the basic syntax?

A simplified form is:

CREATE MATERIALIZED VIEW LOG ON table_name
[WITH ...]
[INCLUDING NEW VALUES]
;

For example:

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

The exact clauses depend on the requirements of the materialized view and refresh method.

6. What does WITH ROWID mean?

WITH ROWID tells Oracle to record the base table's ROWID in the materialized view log.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

This can be useful when Oracle needs ROWID information for FAST refresh.

Think:

WITH ROWID
↓
Record row location information

7. What does WITH PRIMARY KEY mean?

It tells Oracle to record information based on the table's primary key.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY;

If:

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

then Oracle can use the primary-key information for refresh processing.

Conceptually:

WITH PRIMARY KEY
↓
Identify changed rows using primary-key information

8. ROWID vs PRIMARY KEY

This is a common interview question.

ROWID

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

Uses ROWID information.

PRIMARY KEY

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY;

Uses primary-key information.

Comparison:

Feature	ROWID	PRIMARY KEY
Identifies row	ROWID	Primary key
Requires PK	❌	✅
Common use	FAST refresh	FAST refresh
Works with PK-based MV	Depends on definition	Yes

As a general design principle, use the form required by your MV's refresh eligibility rather than choosing arbitrarily.

9. Can an MV log contain columns?

Yes.

Suppose:

CREATE TABLE sales (
    sale_id     NUMBER PRIMARY KEY,
    product_id  NUMBER,
    customer_id NUMBER,
    amount      NUMBER
);

You can create a log containing specific columns:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, customer_id, amount);

These columns provide information needed by a FAST-refreshable MV.

10. Why would I specify columns in the MV log?

Suppose your materialized view uses:

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

Oracle may need information about:

product_id
amount

for incremental refresh.

A log can therefore include the required columns.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY
(product_id, amount);

The exact columns required depend on the MV query and Oracle's FAST refresh rules.

11. What does INCLUDING NEW VALUES mean?

This is important for UPDATE operations.

Example:

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

INCLUDING NEW VALUES tells Oracle to capture the new values of columns when they are updated.

For example:

UPDATE sales
SET amount = 1000
WHERE sale_id = 10;

Conceptually, the log can contain information about the new value:

OLD amount → 500
NEW amount → 1000

This can be required for certain FAST refresh scenarios.

12. Why are old and new values important?

Consider:

UPDATE sales
SET amount = 1000
WHERE sale_id = 10;

An aggregate MV might previously contain:

SUM(amount) = 5000

After the update:

500 → 1000

The aggregate needs to account for:

Remove old value = 500
Add new value    = 1000

Therefore, knowing the new value can be important for incremental maintenance.

13. Does every MV log need INCLUDING NEW VALUES?

No.

It depends on the MV definition and FAST refresh requirements.

Don't memorize:

FAST refresh → always INCLUDING NEW VALUES

Instead:

Use INCLUDING NEW VALUES when the materialized view's FAST refresh requirements need updated column values.

14. What happens when data changes in the base table?

Suppose:

INSERT INTO sales
VALUES (1, 101, 500);

Oracle records the relevant change in the MV log.

Conceptually:

SALES
|
+---- INSERT
|
↓
MV LOG
|
+---- change information

Later:

DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');

Oracle can use that change information for FAST refresh.

15. Does an MV log contain the entire base table?

No.

This is an important distinction.

The base table:

SALES

---

1
2
3
4
5
...
1,000,000

The MV log records changes, for example:

SALE_ID   DML

---

100       INSERT
250       UPDATE
700       DELETE

Conceptually:

Base table = current data

MV log = change information

16. Does an MV log replace the base table?

No.

It is an auxiliary object used to support materialized-view maintenance.

Base table
↓
MV log
↓
FAST refresh
↓
Materialized view

The base table remains the source of truth.

17. Example: Creating an MV log

Let's start with:

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

Insert data:

INSERT INTO sales VALUES (1, 101, 500);
INSERT INTO sales VALUES (2, 101, 300);
INSERT INTO sales VALUES (3, 102, 1000);

COMMIT;

Create MV log:

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

Now Oracle can record relevant changes for supported FAST refresh scenarios.

18. Example: MV using the MV log

Create:

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
AS
SELECT product_id,
COUNT(*) AS sale_count,
SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Now:

INSERT INTO sales
VALUES (4, 101, 200);

COMMIT;

The MV isn't automatically refreshed because it is:

ON DEMAND

Later:

BEGIN
DBMS_MVIEW.REFRESH(
    'MV_PRODUCT_SALES',
    'F'
);
END;
/

The FAST refresh can use the MV log.

19. Can an MV log be used with ON COMMIT?

Yes.

A common pattern is:

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

Then:

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
AS
SELECT product_id,
COUNT(*) AS sale_count,
SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Conceptually:

DML
↓
MV log records change
↓
COMMIT
↓
FAST refresh
↓
MV updated

20. What is the difference between MV log and ON DEMAND?

They are completely different concepts.

MV log

WHAT?
↓
Records changes

ON DEMAND

WHEN?
↓
Refresh when requested

For example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY;

and:

CREATE MATERIALIZED VIEW mv_sales
REFRESH FAST
ON DEMAND
AS
SELECT ...;

Here:

MV Log       → records changes
FAST         → incremental refresh
ON DEMAND    → refresh when requested

21. What is the difference between MV log and materialized view?

MV log

SALES
↓
Changes
↓
MV_LOG

Materialized view

SALES
↓
SELECT query
↓
MV_SALES

Think:

MV = result
MV log = change history needed for refresh

22. Does an MV log improve query performance?

Not directly.

The MV log exists primarily to support materialized view maintenance, especially FAST refresh.

It isn't designed as a reporting table.

Don't use:

MV LOG

as a replacement for:

INDEX

or:

MATERIALIZED VIEW

23. Does an MV log increase storage usage?

Yes.

Every change that needs to be recorded can add data to the MV log.

For a high-volume table:

SALES
Millions of changes
↓
MV LOG
Large amount of change information

Therefore, MV logs require storage and maintenance.

24. Does an MV log increase DML overhead?

Yes, potentially.

When a row changes:

INSERT/UPDATE/DELETE
↓
Base table modified
+
MV log information recorded

So there is additional work associated with maintaining the log.

This should be considered when designing high-volume OLTP systems.

25. Does an MV log get automatically purged?

Oracle manages MV log data based on materialized-view refresh requirements, but the exact purge behavior depends on the materialized views using the log and their refresh state.

The important idea is:

MV log
↓
Changes needed by MV refresh
↓
After changes are no longer needed
↓
Oracle can purge eligible log data

You should not assume that the MV log behaves like an ordinary application audit table.

26. Can multiple materialized views use the same MV log?

Yes.

A single base table can have a materialized view log that supports multiple materialized views, provided the log contains the information needed by those MVs.

Example:

SALES
|
+------+------+
|             |
↓             ↓
MV LOG        MV LOG data
|
+-----+-----+
|           |
↓           ↓
MV_SALES1   MV_SALES2

You don't normally create a separate MV log for every MV on the same base table.

27. Can an MV log be created on a table with a primary key?

Yes.

Example:

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    name        VARCHAR2(100),
    city        VARCHAR2(100)
);

Create:

CREATE MATERIALIZED VIEW LOG ON customers
WITH PRIMARY KEY;

28. Can an MV log be created on a table without a primary key?

Yes, depending on the refresh requirements.

For example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

This records ROWID information instead of relying on a primary key.

But the MV definition must be compatible with the chosen refresh mechanism.

29. What is SEQUENCE in an MV log?

You may see:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE;

SEQUENCE provides sequencing information for changes recorded in the log.

This can be useful for certain FAST refresh scenarios, particularly where ordering/change information matters.

Don't confuse:

SEQUENCE

with:

CREATE SEQUENCE ...

in the normal application sense.

30. Why do I sometimes see ROWID, SEQUENCE together?

A common MV log definition is:

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

Each part has a purpose:

ROWID
↓
Row identification

SEQUENCE
↓
Change sequencing information

INCLUDING NEW VALUES
↓
Capture new values for relevant updates

The exact required combination depends on the MV's FAST refresh requirements.

31. Can I add an MV log after creating the materialized view?

Yes, but you need to consider whether the existing MV can then become FAST-refreshable and whether it needs a complete refresh first.

Typical sequence:

Create base table
↓
Create MV log
↓
Create FAST-refreshable MV

For an existing MV, changing the log configuration may require additional administration and validation.

32. How do I check MV logs?

You can query Oracle's data dictionary.

For example:

SELECT log_owner,
master,
log_table
FROM all_mview_logs;

For your own schema:

SELECT log_owner,
master,
log_table
FROM user_mview_logs;

This helps identify:

Base table
↓
MV log

33. How do I see the columns in an MV log?

You can inspect the MV log table itself using the data dictionary.

For example:

SELECT column_name,
data_type
FROM user_tab_columns
WHERE table_name = 'MLOG$_SALES';

Oracle commonly gives MV log tables names such as:

MLOG$_SALES

Don't hard-code this naming convention into application logic; use the dictionary views when you need to identify the log.

34. What is MLOG$_SALES?

When you create:

CREATE MATERIALIZED VIEW LOG ON sales
...

Oracle creates an internal log table, commonly named something like:

MLOG$_SALES

Conceptually:

SALES
↓
MLOG$_SALES

This is the physical object that stores the logged change information.

35. Can I directly query MLOG$_SALES?

Technically, depending on privileges, the underlying log table may be visible, but you generally should not treat it as an application table.

Instead, use Oracle's documented materialized-view log mechanisms and data dictionary views.

For administration:

SELECT *
FROM user_mview_logs;

is preferable to depending on internal naming.

36. What happens if I drop an MV log?

If a materialized view depends on that log for FAST refresh, dropping the log can make FAST refresh unavailable or cause subsequent refresh operations to fail.

Example:

DROP MATERIALIZED VIEW LOG ON sales;

Afterward:

MV
↓
FAST refresh
↓
Required log missing
↓
FAST refresh may fail

Therefore, don't drop an MV log without checking which MVs depend on it.

37. What happens if I truncate the MV log?

You should not treat an MV log like a normal staging table and truncate it arbitrarily.

The logged changes may be required by one or more materialized views for their next FAST refresh.

If the required change information is removed prematurely:

MV LOG
↓
Required changes deleted
↓
FAST refresh may no longer be possible

Oracle's materialized-view refresh mechanism manages log data as part of MV maintenance.

38. Does COMPLETE refresh need an MV log?

No, not for the purpose of complete refresh.

A COMPLETE refresh recomputes the materialized view query.

Conceptually:

COMPLETE
↓
Run MV query again
↓
Rebuild result

Therefore:

FAST refresh → MV log often important
COMPLETE      → MV log isn't needed to calculate the complete result

39. Does FORCE refresh need an MV log?

FORCE tries FAST refresh when possible and otherwise uses COMPLETE refresh.

Therefore:

FORCE
↓
Try FAST
↓
If FAST possible
↓
Use MV log/change information as required

If FAST not possible
↓
COMPLETE

So an MV log can be important if you want FORCE to take the FAST path.

40. Can MV logs record INSERT, UPDATE and DELETE?

Yes.

Conceptually:

INSERT → log change
UPDATE → log change
DELETE → log change

For example:

INSERT INTO sales VALUES (10, 101, 500);

UPDATE sales
SET amount = 600
WHERE sale_id = 10;

DELETE FROM sales
WHERE sale_id = 10;

The log supports the refresh mechanism in determining what happened.

41. What is a common MV log example for an aggregate MV?

Base table:

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

MV log:

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

MV:

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
AS
SELECT product_id,
COUNT(*) AS sale_count,
SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Refresh:

BEGIN
DBMS_MVIEW.REFRESH(
    list   => 'MV_PRODUCT_SALES',
    method => 'F'
);
END;
/

Conceptually:

SALES
↓
MATERIALIZED VIEW LOG
↓
Changes since last refresh
↓
FAST refresh
↓
MV_PRODUCT_SALES

42. What happens when an UPDATE changes an aggregated column?

Suppose:

Product 101
Total = 1000

Then:

UPDATE sales
SET amount = amount + 100
WHERE sale_id = 1;

For FAST refresh, Oracle needs enough information to determine how the aggregate changes.

Conceptually:

Old value = 500
New value = 600

Aggregate:
1000 - 500 + 600
↓
1100

The MV log provides the change information required by the refresh mechanism.

43. What is the relationship between MV logs and FAST refresh?

This is the most important concept:

FAST REFRESH
|
↓
What changed?
|
↓
MV LOG / logs
|
↓
Apply incremental changes
|
↓
MV

Without the necessary change information, Oracle may not be able to perform FAST refresh.

44. What is the relationship between MV logs and ON COMMIT?

For a typical FAST + ON COMMIT design:

Base table
↓
DML
↓
MV log records change
↓
COMMIT
↓
FAST refresh
↓
Materialized view

So:

MV log       = WHAT changed
ON COMMIT    = WHEN to refresh
FAST         = HOW to refresh

This three-part distinction is excellent for interviews.

45. Can an MV log be used with ON DEMAND?

Yes.

Example:

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

Then:

CREATE MATERIALIZED VIEW mv_sales
REFRESH FAST
ON DEMAND
AS
SELECT product_id,
SUM(amount)
FROM sales
GROUP BY product_id;

The MV log supports FAST refresh, while ON DEMAND controls when refresh is requested.

46. MV Log vs Trigger

This is another useful interview comparison.

Trigger

DML
↓
Trigger executes
↓
Custom PL/SQL

MV Log

DML
↓
Oracle records change information
↓
Used by MV refresh mechanism

MV logs are specifically designed to support materialized-view maintenance.

47. Can I use a trigger instead of an MV log?

Not as a simple replacement.

You could build custom change-tracking logic with triggers, but that doesn't mean Oracle's FAST refresh mechanism can simply use your custom table as an MV log.

For Oracle FAST refresh, use the supported:

CREATE MATERIALIZED VIEW LOG

mechanism.

48. What are the disadvantages of MV logs?

Main considerations:

49. Additional storage

Base table changes
↓
MV log rows
↓
Storage usage

50. DML overhead

Changes must be logged.

51. Administration

You need to monitor:

• Log size
• Refresh frequency
• Dependent MVs
• Refresh failures

52. Design complexity

The log must contain information appropriate for the MV's FAST refresh requirements.

53. What are the advantages?

FAST refresh

Instead of recalculating the complete MV:

Entire base table
↓
Recalculate everything

Oracle can use:

Changes since last refresh
↓
Incremental maintenance

Better for large datasets.

Especially when:

Large base table
-
Small number of changes
Supports frequent refresh

It can be particularly useful for:

FAST + ON COMMIT

or:

FAST + ON DEMAND

50. Common MV Log interview traps

Trap 1

Is an MV log the same as a materialized view?

❌ No.

MV      = query result
MV Log  = change information

Trap 2

Is an MV log required for every materialized view?

❌ No.

It is primarily associated with FAST refresh requirements.

Trap 3

Does COMPLETE refresh require an MV log?

❌ No.

Complete refresh can recalculate the MV query.

Trap 4

Does ON DEMAND require an MV log?

❌ No.

ON DEMAND only specifies refresh timing.

Trap 5

Does ON COMMIT itself mean an MV log is always required?

❌ Don't generalize that way.

The refresh method and MV definition determine what information is required. Typical FAST + ON COMMIT designs use an MV log.

Trap 6

Does INCLUDING NEW VALUES mean the MV log stores only new rows?

❌ No.

It refers to capturing new column values for relevant UPDATE operations.

Trap 7

Can I truncate an MV log whenever I want?

❌ No.

The logged changes may still be required for FAST refresh.

51. Important MV Log clauses

Remember these:

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

WITH ROWID

Record ROWID information

WITH PRIMARY KEY

Record primary-key information

SEQUENCE

Record sequencing information for changes

Column list

Specify columns needed for refresh

Example:

(product_id, amount)

INCLUDING NEW VALUES

Capture new values for relevant UPDATE operations

52. How do I monitor MV logs?

Start with:

SELECT log_owner,
master,
log_table
FROM user_mview_logs;

Then inspect the log table metadata:

SELECT column_name,
data_type
FROM user_tab_columns
WHERE table_name = 'MLOG$_SALES';

You can also monitor your materialized views:

SELECT mview_name,
refresh_mode,
refresh_method,
staleness,
last_refresh_type,
last_refresh_date
FROM user_mviews;

This gives you the relationship:

MV definition
+
MV log
+
Refresh status

53. Complete example: FAST + ON DEMAND

Base table

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

MV log

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

Materialized view

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
AS
SELECT product_id,
COUNT(*) AS sale_count,
SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Change data

INSERT INTO sales VALUES (1, 101, 500);
INSERT INTO sales VALUES (2, 101, 300);

COMMIT;

Refresh

BEGIN
DBMS_MVIEW.REFRESH(
    list   => 'MV_PRODUCT_SALES',
    method => 'F'
);
END;
/

Conceptually:

INSERT/UPDATE/DELETE
↓
MV LOG
↓
DBMS_MVIEW.REFRESH
↓
FAST refresh
↓
MV

54. Complete example: FAST + ON COMMIT

Create the log:

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

Create the MV:

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
AS
SELECT product_id,
COUNT(*) AS sale_count,
SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Then:

INSERT INTO sales
VALUES (10, 101, 500);

COMMIT;

Conceptually:

INSERT
↓
MV LOG
↓
COMMIT
↓
FAST refresh
↓
MV updated

55. The most important concept to memorize

Think of an MV refresh as answering three questions:

56. WHAT changed?

↓
MV LOG

57. HOW should the MV be refreshed?

↓
FAST / COMPLETE / FORCE

58. WHEN should it be refreshed?

↓
ON COMMIT / ON DEMAND

For example:

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

then:

CREATE MATERIALIZED VIEW mv_sales
REFRESH FAST
ON DEMAND
AS
SELECT product_id,
SUM(amount)
FROM sales
GROUP BY product_id;

means:

MV LOG
↓
WHAT changed?

FAST
↓
HOW to refresh?

ON DEMAND
↓
WHEN to refresh?

56. Final MV Log cheat sheet

Concept	Meaning
MV Log	Records base-table changes
WITH ROWID	Records ROWID information
WITH PRIMARY KEY	Records PK information
SEQUENCE	Records change sequencing information
Column list	Records specified columns needed for refresh
INCLUDING NEW VALUES	Captures new values for relevant updates
FAST refresh	Uses change information for incremental refresh
COMPLETE refresh	Recomputes the MV
ON COMMIT	Refresh associated with commit
ON DEMAND	Refresh when requested
MLOG$_...	Common physical name of an MV log table
MV Log ≠ MV	Log stores changes; MV stores query result

One-line interview answer

An Oracle Materialized View Log is a change-tracking object on a base table that records information required by materialized views, primarily to enable efficient FAST refresh instead of recomputing the entire materialized view.

The ultimate memory trick

BASE TABLE
|
| DML
↓
MV LOG
"WHAT changed?"
|
↓
FAST REFRESH
"HOW to update?"
|
↓
MATERIALIZED VIEW

And:

ON COMMIT  → WHEN? At commit
ON DEMAND  → WHEN? When requested
MV LOG     → WHAT changed?
FAST       → HOW to refresh?