Search code examples
oracle-databaseoracle12cmaterialized-views

Refresh fast on commit with groupby and where clause


I am able to successfully create a materialized view such as below however when I look at MV_CAPABILITIES_TABLE, it is telling me that REFRESH_FAST_AFTER_ONETAB_DML and REFRESH_FAST_ANY_DML is not possible.

CREATE MATERIALIZED VIEW MV_contacts_table
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT t.ID AS ID,
MAX(DECODE(t.FieldName, 'FirstName', t.FieldValue)) FirstName, 
MAX(DECODE(t.FieldName, 'LastName', t.FieldValue)) LastName,
COUNT(*) AS NUM_FIELDS FROM Contacts_Table t
WHERE t.FieldName = 'FirstName' OR t.FieldName = 'LastName'
GROUP BY t.ID

Here the MV_CAPABILITIES_TABLE

PCT                             N               
REFRESH_COMPLETE                Y               
REFRESH_FAST                    Y               
REWRITE                         N               
PCT_TABLE                       N   CONTACTS_TABLE  675 2068    relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT       Y               
REFRESH_FAST_AFTER_ONETAB_DML   N                       2086    mv uses the MIN or MAX aggregate functions
REFRESH_FAST_AFTER_ANY_DML      N                       2161    see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT                N                       2157    PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH         N                       2159    query rewrite is disabled on the materialized view
REWRITE_PARTIAL_TEXT_MATCH      N                       2159    query rewrite is disabled on the materialized view
REWRITE_GENERAL                 N                       2159    query rewrite is disabled on the materialized view
REWRITE_PCT                     N                       2158    general rewrite is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE               N   CONTACTS_TABLE  675 2068    relation is not a partitioned table

If I remove the where clause the REFRESH_FAST_AFTER_ANY_DML is possible. Is there any possibility that this SQL query can be tweaked so that my materialized view set to REFRESH_FAST_AFTER_ANY_DML with including Where clause?


Solution

  • This is a restriction of materialized views:

    A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.

    The max/min fast refresh after delete or mixed DML does not have the same behavior as the insert-only case. It deletes and recomputes the max/min values for the affected groups. You need to be aware of its performance impact.

    (emphasis mine).

    So you need to find some other way to achieve your goal.