'Fast Refreshable MView creation ORA-12015: cannot create a fast refresh materialized view from a complex query

I want to create m-view which keeps the aggregates of cash from transaction history.

The query is as follows.

CREATE MATERIALIZED VIEW MV_CASH_STATS

BUILD IMMEDIATE REFRESH FAST ON DEMAND 
AS

( -- Total aggregates 
    SELECT
        'total' AS CODE
        , COUNT(T.CASH_IN) AS DEPOSITS
        , COUNT(T.CASH_OUT) AS WITHDRAWS
        , SUM(T.CASH_IN) AS DEPOSIT
        , SUM(T.CASH_OUT) AS WITHDRAW
    FROM
        TB_CASH_LOG T
)
UNION ALL
( -- Aggregates by account grades
    SELECT
        'grade-' || T1.GRADE AS CODE
        , COUNT(T.CASH_IN) AS DEPOSITS
        , COUNT(T.CASH_OUT) AS WITHDRAWS
        , SUM(T.CASH_IN) AS DEPOSIT
        , SUM(T.CASH_OUT) AS WITHDRAW
    FROM
        TB_CASH_LOG T
        INNER JOIN TB_ACCOUNT T1 ON
            T.ACCOUNT_ID = T1.ID
    GROUP BY T1.GRADE
)

When i execute this, the following error occurs.

> ORA-12015: cannot create a fast refresh materialized view from a complex query

(I created m-view log tables for both TB_CASH_LOG and TB_ACCOUNT)

The Total aggregates part is ok, but the later part - Aggregates by account grades - makes the error.

I've read Oracle basic materialized view docs, and thinking that INNER JOIN or GROUP BY is the error point.

If it is true, fast refresh m-view with inner join and group by is impossible?
What is wrong with my m-view declaration sql?

p.s. I'm working on oracle 18c



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source