Oracle: Insert Materialized View

Question

In Oracle, what is insert-only materialized view?

I have the following materialized view that uses the aggregate function MAX :

 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 Warehouse Guide , it should only be an insert materialized view:

If the materialized view has one of the following meanings, then fast updates are only supported on regular DML inserts and direct loads.

• Materialized views with MIN or MAX aggregates
• Materialized views having SUM (expr) but not COUNT (expr)
• Materialized views without COUNT (*)

Such a materialized representation is called a materialized representation only in the form.

I would expect such a materialized view to be updated quickly only when pasted into the main table. Instead, DBMS_MVIEW.EXPLAIN_MVIEW tells me that this materialized view always updates quickly:

 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 a quick transaction update works even after updates in the main table.

Repeat:

• What is the difference between a materialized view just for insertion and a simple, quickly updated materialized view?
• Why is Oracle documentation for me? :)

Using Oracle 11.2 Enterprise Edition

Answer 1

The difference between a regular fast updatable mview and an mview-insert - as you said - that an updatable mview just for insert can be updated quickly after pasting a statemenet, and not after any other DML operation (like deleting and updating)

I assume that the logic of the restriction is that when you update an existing value, Oracle is not able to know, only in the mlog table, what is the new max (it will need to save some rank in order to do this).

Regarding the feature table, this is odd. Check the page - it 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 

Did you try to perform a quick update after the update?