Materialized View with analytic function in Oracle

Question

In the oracle docs I found that one can create materialized views with analytic functions:

If you use an analytic window function or the MODEL clause, the partition key column or the partition marker or ROWID or join dependent expression must be present in their respective PARTITION BY subclauses.

(see https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dwhsg/advanced-materialized-views.html#GUID-CE717BD7-15B7-4C8B-A172-6C50F5A802E1)

However, with the following example I always receive ORA-12052 "cannot fast refresh materialized view %s.%s".

I want to create a materialized view on a single table. I created a materialized view log on the underlying table, specified the rowid, and then created the materialized view containing the analytic function lead:

CREATE TABLE CUSTOMER_EVENT (
    CUSTOMER_ID    NUMBER        NOT NULL,
    EVENT_DATE     DATE          NOT NULL,
    CUSTOMER_ATTR  VARCHAR(30)   NOT NULL,
    PRIMARY KEY (CUSTOMER_ID, EVENT_DATE)
)
PARTITION BY HASH(CUSTOMER_ID)
PARTITIONS 4;

CREATE MATERIALIZED VIEW LOG ON CUSTOMER_EVENT
WITH ROWID (
    CUSTOMER_ID,
    EVENT_DATE
) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW CUSTOMER_HISTORY
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
    CUSTOMER_ID,
    CUSTOMER_ATTR,
    EVENT_DATE AS VALID_FROM,
    NVL(LEAD(EVENT_DATE) OVER (
        PARTITION BY
            CUSTOMER_ID
        ORDER BY
            EVENT_DATE
    ), DATE'9999-12-31') AS VALID_TO
FROM
    CUSTOMER_EVENT;

The last statement always fails with ORA-12052 "cannot fast refresh materialized view %s.%s".

Does anyone have a hint what I'm doing wrong?

From the docs, General Restrictions on Fast Refresh: "It cannot contain analytic functions (for example, RANK) in the SELECT clause." — Alex Poole

Sayan Malakshinov Sayan Malakshinov · Accepted Answer · 2021-05-05T17:12:14

It's always better to use DBMS_MVIEW.EXPLAIN_MVIEW to diagnose such problems with mviews.

It's pretty easy:

create table MV_CAPABILITIES_TABLE: execute @?/rdbms/admin/utlxmv.sql in sqlplus
grant execute on dbms_mview to {your_user} as sys
execute call dbms_mview.explain_mview('{query or mview name}');
and then select * from MV_CAPABILITIES_TABLE

For last 2 steps I use my own script: https://github.com/xtender/xt_scripts/blob/master/mviews/explain.sql

For example for your mview it shows:

CAPABILITY_NAME                P RELATED_TEXT    MSGNO MSGTXT                                                                                SEQ
------------------------------ - --------------- ----- ----------------------------------------------------------------------------------- -----
PCT                            N                                                                                                               1
REFRESH_COMPLETE               Y                                                                                                            1002
REFRESH_FAST                   N                                                                                                            2003
REWRITE                        Y                                                                                                            3004
PCT_TABLE                      N CUSTOMER_EVENT   2070 PCT not supported with this type of partitioning                                     4005
REFRESH_FAST_AFTER_INSERT      N                  2047 window function in mv                                                                5006
REFRESH_FAST_AFTER_ONETAB_DML  N                  2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled                             6007
REFRESH_FAST_AFTER_ANY_DML     N                  2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled                         7008
REFRESH_FAST_PCT               N                  2157 PCT is not possible on any of the detail tables in the materialized view             8009
REWRITE_FULL_TEXT_MATCH        Y                                                                                                            9010
REWRITE_PARTIAL_TEXT_MATCH     Y                                                                                                           10011
REWRITE_GENERAL                Y                                                                                                           11012
REWRITE_PCT                    N                  2158 general rewrite is not possible or PCT is not possible on any of the detail tables  12013
PCT_TABLE_REWRITE              N CUSTOMER_EVENT   2070 PCT not supported with this type of partitioning                                    13014

Materialized View with analytic function in Oracle

1 Answers