Oracle: insert-only materialized view

Question

In Oracle, what is an insert-only materialized view?

I have the following materialized view, which uses a MAX aggregate function:

CREATE MATERIALIZED VIEW VM_FAST_MAX
REFRESH FAST ON COMMIT
AS
SELECT   d.ID_INPUT, MAX(d.ID_LOAD) AS ID_LOAD, COUNT(*) AS CNT
FROM     MASTER_TABLE d
GROUP BY d.ID_INPUT;

According to the Oracle Data Warehousing Guide, it should be an insert-only materialized view:

If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.

• Materialized views with MIN or MAX aggregates
• Materialized views which have SUM(expr) but no COUNT(expr)
• Materialized views without COUNT(*)

Such a materialized view is called an insert-only materialized view.

I would expect such a materialized view to be fast refreshable only when inserting on the master table. Instead, DBMS_MVIEW.EXPLAIN_MVIEW tells me that this materialized view is always fast refreshable:

EXEC DBMS_MVIEW.EXPLAIN_MVIEW('VM_FAST_MAX');
SELECT CAPABILITY_NAME, POSSIBLE
FROM MV_CAPABILITIES_TABLE
WHERE MVNAME = 'VM_FAST_MAX';

CAPABILITY_NAME                P
------------------------------ -
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ANY_DML     Y

And fast refresh on commit works even after updates on the master table.

To recap:

• What is the difference between an insert-only materialized view and a plain, fast refreshable materialized view?
• Why is the Oracle documentation lying to me? :)

Using Oracle 11.2 Enterprise Edition.

Answer 1

The difference between a regular fast refreshable mview and an insert-only mview is - as you said - that an insert-only refreshable mview can only be fast refreshed after an insert statemenet and not after any other DML operation (such as delete & update)

I assume that the logic behind the restriction is that when you update an existing value Oracle has no way of knowing, by the mlog table alone, what is the new max (it will have to keep some sort of rank to do that).

Regarding the capabilities table - this is odd. Check this page - this did the same test but in their example they got

Capable of:
  REFRESH_FAST
  REFRESH_FAST_AFTER_INSERT
Not Capable of:
  REFRESH_FAST_AFTER_ONETAB_DML
    AMT_SUM
    SUM(expr) without COUNT(expr)
  REFRESH_FAST_AFTER_ONETAB_DML
    COUNT(*) is not present in the select list
  REFRESH_FAST_AFTER_ANY_DML
    see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

Have you tried performing a fast refresh after an update?