Charlie Siu, May 09, 2017 - 3:09 am UTC
It can created after adding those 4 columns but it does not refresh on commit
Charlie Siu, May 09, 2017 - 3:11 am UTC
When i try to insert / update the column on the underlying table (update the columns that is in the materialized view log).
the materialized view did not refresh...do i missing something?
More info to provide
Charlie Siu, May 09, 2017 - 3:31 am UTC
CREATE MATERIALIZED VIEW LOG ON EXP_DC_HST
WITH SEQUENCE, ROWID
(ACTIVITY_DESCRIPTION, ACTIVITY_TYPE, CD1, CD2, CD3, CD4, Capture_date)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW EXP_DC_AMEND_MVIEW
PARALLEL
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
select H.ACTIVITY_DESCRIPTION, H.ACTIVITY_TYPE, H.CD1, H.CD2, H.CD3, H.CD4, H.CD1|| H.CD2|| trim(H.CD3)||'-'||LPAD(H.CD4,2,0) as EXP_DC_NUMBER,
max(H.Capture_date) as DC_STATUS_TIME
from EXP_DC_HST H where H.ACTIVITY_TYPE = 'AMENDMENT'
AND INSTR(H.ACTIVITY_DESCRIPTION, 'INFORMAL') = 0
group by H.ACTIVITY_DESCRIPTION, H.ACTIVITY_TYPE, H.CD1, H.CD2, H.CD3, H.CD4;
when i tried to insert a new record in EXP_DC_HST and commit, the MVIEW did not refresh..
May 15, 2017 - 7:56 pm UTC
Can we have a *full* test case please.
source table + sample data
So we can run your mview statements
mathguy, July 12, 2021 - 3:19 pm UTC
Concatenation,
TRIM
etc. have nothing to do with fast refresh of
mv's; they don't cause any trouble. The issue in this problem is that the OP aggregates over
capture_date
. For the
mv to be fast refreshable, the
SELECT
list must include
count(capture_date)
, and the OP's
SELECT
list does not.
It is trivial to build a fast refreshable
mv over
SCOTT.EMP
to prove this. (Both that "concatenation and
TRIM
and such are no obstacle" and that the issue is the missing
COUNT()
in the
SELECT
list.)
July 12, 2021 - 5:28 pm UTC
Good points