'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 |
|---|
