7

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.

Danilo Piazzalunga
  • 7,590
  • 5
  • 49
  • 75

1 Answers1

2

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?

Beryllium
  • 12,808
  • 10
  • 56
  • 86
haki
  • 9,389
  • 15
  • 62
  • 110
  • I did, and it worked. Needless to say, this has puzzled me. I am definitely going to test it again. – Danilo Piazzalunga Jun 11 '13 at 20:50
  • The `MV_CAPABILITIES_TABLE` does not contain any row for the `REFRESH_FAST_AFTER_ONETAB_DML` capability. I assume that the insert-only restrictions only apply on materialized view based on multiple tables. – Danilo Piazzalunga Jun 12 '13 at 09:22
  • you should also trace the session and see that it acctually perform a fast refresh. if you attemp to performe fast refresh and oracle is not capeable of doing that he will sometime just perform a complete refresh. try and test different scenerios like updating an existsing max to a lower / higher value , deleting the max etc ... – haki Jun 12 '13 at 11:17
  • I have told Oracle to perform a fast refresh _on commit_. I would expect an error if fast refresh didn't work. – Danilo Piazzalunga Jun 13 '13 at 08:18