I am trying to create the following Materialized View with ENABLE ON QUERY COMPUTATION but I can't find where is the issue.
I have three master tables with the corresponding materialized view logs and the columns needed. Can anyone help me ?
Thank you
create materialized view log on alfaods.OdsReceivable with rowid, sequence ( dueDate , recvChargeTypeId, scheduleId , amount ) , primary key including new values for fast refresh;
create materialized view log on alfaods.OdsChargeType with rowid, sequence ( code ), primary key including new values for fast refresh;
create materialized view log on alfaods.OdsScheduleMain with rowid, primary key including new values for fast refresh;
Then the MV is
create materialized view alfaods.mv_max_fn_date
TABLESPACE TBDATA
CACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
SELECT max(receivable.dueDate) as finalDate,
schedule.id as scheduleId ,
receivable.recvChargeTypeId as recvChargeTypeId,
receivable.scheduleId as receivableSchId ,
receivable.amount as recamount ,
chargeType.code as ChargTypecode
FROM ALFAODS.OdsReceivable receivable
INNER JOIN ALFAODS.OdsChargeType chargeType on receivable.recvChargeTypeId = chargeType.id
INNER JOIN ALFAODS.OdsScheduleMain schedule on receivable.scheduleId = schedule.id
where
receivable.amount NOT IN (0.01, 0.00)
AND chargeType.code = 2
group by schedule.id , receivable.recvChargeTypeId , receivable.scheduleId , receivable.amount , chargeType.code
;
When I try to create it , I got this error
SQL> create materialized view alfaods.mv_max_fn_date
TABLESPACE TBDATA
CACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
SELECT max(receivable.dueDate) as finalDate,
schedule.id as scheduleId ,
receivable.recvChargeTypeId as recvChargeTypeId,
receivable.scheduleId as receivableSchId ,
receivable.amount as recamount ,
chargeType.code as ChargTypecode
FROM ALFAODS.OdsReceivable receivable
INNER JOIN ALFAODS.OdsChargeType chargeType on receivable.recvChargeTypeId = chargeType.id
INNER JOIN ALFAODS.OdsScheduleMain schedule on receivable.scheduleId = schedule.id
where
receivable.amount NOT IN (0.01, 0.00)
AND chargeType.code = 2
group by schedule.id , receivable.recvChargeTypeId , receivable.scheduleId , receivable.amount , chargeType.code
25 ;
AND chargeType.code = 2
*
ERROR at line 23:
ORA-32361: cannot ENABLE ON QUERY COMPUTATION for the materialized view
Elapsed: 00:00:00.00
SQL>
It looks like there is a problem with the where condition, so I tested it without where
SQL> create materialized view alfaods.mv_max_fn_date
TABLESPACE TBDATA
CACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
SELECT max(receivable.dueDate) as finalDate,
schedule.id as scheduleId ,
receivable.recvChargeTypeId as recvChargeTypeId,
receivable.scheduleId as receivableSchId ,
receivable.amount as recamount ,
chargeType.code as ChargTypecode
FROM ALFAODS.OdsReceivable receivable
INNER JOIN ALFAODS.OdsChargeType chargeType on receivable.recvChargeTypeId = chargeType.id
INNER JOIN ALFAODS.OdsScheduleMain schedule on receivable.scheduleId = schedule.id
--where
--receivable.amount NOT IN (0.01, 0.00)
--AND chargeType.code = 2
group by schedule.id , receivable.recvChargeTypeId , receivable.scheduleId , receivable.amount , chargeType.code
;
INNER JOIN ALFAODS.OdsScheduleMain schedule on receivable.scheduleId = schedule.id
*
ERROR at line 20:
ORA-32361: cannot ENABLE ON QUERY COMPUTATION for the materialized view
UPDATE
I ran the DBMS_MVIEW.EXPLAIN_MVIEW
set serveroutput on size unlimited echo on long 99999999 longchunksize 99999999
declare
a sys.ExplainMVArrayType;
begin
dbms_mview.explain_mview('SELECT receivable.recvChargeTypeId as recvChargeTypeId,
receivable.scheduleId as scheduleId ,
receivable.amount as recamount ,
chargeType.id as ChargeID,
max(receivable.dueDate) as finalDate
FROM ALFAODS.OdsReceivable receivable
INNER JOIN ALFAODS.OdsChargeType chargeType on receivable.recvChargeTypeId = chargeType.id
group by receivable.recvChargeTypeId , receivable.scheduleId , receivable.amount , chargeType.id',a);
dbms_output.put_line('Explain MV '
|| a(1).mvowner || '.' || a(1).mvname);
for i in 1..a.count loop
dbms_output.put_line(
rpad(a(i).capability_name, 30)
|| ' [' || case a(i).possible
when 'T' then 'TRUE'
when 'F' then 'FALSE'
else a(i).possible
end || ']'
|| case when a(i).related_num != 0 then
' ' || a(i).related_text
|| ' (' || a(i).related_num || ')'
end
|| case when a(i).msgno != 0 then
' ' || a(i).msgtxt
|| ' (' || a(i).msgno || ')'
end
);
end loop;
end;
/
Explain MV .
PCT [FALSE]
REFRESH_COMPLETE [TRUE]
REFRESH_FAST [FALSE]
REWRITE [TRUE]
REFRESH_FAST_AFTER_INSERT [FALSE] join may produce duplicate rows in mv
(2059)
REFRESH_FAST_AFTER_INSERT [FALSE] MV is not fast refreshable even with view
merging (2154)
REFRESH_FAST_AFTER_ONETAB_DML [FALSE] FINALDATE (193) mv uses the MIN or MAX
aggregate functions (2086)
REFRESH_FAST_AFTER_ONETAB_DML [FALSE] see the reason why
REFRESH_FAST_AFTER_INSERT is disabled (2146)
REFRESH_FAST_AFTER_ONETAB_DML [FALSE] mv uses the MIN or MAX aggregate
functions (2086)
REFRESH_FAST_AFTER_ANY_DML [FALSE] see the reason why
REFRESH_FAST_AFTER_ONETAB_DML is disabled (2161)
REFRESH_FAST_PCT [FALSE] PCT FAST REFRESH is not possible if query
contains an inline view (2196)
REWRITE_FULL_TEXT_MATCH [TRUE]
REWRITE_PARTIAL_TEXT_MATCH [TRUE]
REWRITE_GENERAL [FALSE] the reason why the capability is disabled
has escaped analysis (2141)
REWRITE_PCT [FALSE] general rewrite is not possible or PCT is
not possible on any of the detail tables (2158)
Finally I could create the MV like this. I had to remove the MAX function from the MV, instead I would use it in the query which was something I wanted to avoid. Anyway, here the script
SQL> @rebmv.sql
SQL> drop materialized view log on alfaods.OdsScheduleMain ;
Materialized view log dropped.
Elapsed: 00:00:00.05
SQL> drop materialized view log on alfaods.OdsChargeType ;
Materialized view log dropped.
Elapsed: 00:00:00.03
SQL> drop materialized view log on alfaods.OdsReceivable ;
Materialized view log dropped.
Elapsed: 00:00:00.04
SQL> drop materialized view alfaods.mv_max_fn_date ;
Materialized view dropped.
Elapsed: 00:00:00.14
SQL>
SQL> create materialized view log on alfaods.OdsScheduleMain with rowid, sequence ( maturityDate,alfascheduleidentifier,scheduleStatus,terminationDate,totalAssetCostFinanced,capitalOutstanding,activationDate,agreementId,invCusId,dealerid,invCompanyId,agrCompanyId ), primary key including new values for fast refresh;
Materialized view log created.
Elapsed: 00:00:00.06
SQL>
SQL> create materialized view log on alfaods.OdsChargeType with rowid, sequence ( code ) , primary key including new values for fast refresh;
Materialized view log created.
Elapsed: 00:00:00.03
SQL>
SQL> create materialized view log on alfaods.OdsReceivable with rowid, sequence ( dueDate,recvChargeTypeId,scheduleId,amount ), primary key including new values for fast refresh;
Materialized view log created.
Elapsed: 00:00:00.02
SQL>
SQL> create materialized view alfaods.mv_max_fn_date
2 TABLESPACE TBDATA
3 CACHE
4 LOGGING
5 NOCOMPRESS
6 NOPARALLEL
7 BUILD IMMEDIATE
8 REFRESH FAST ON DEMAND
9 ENABLE QUERY REWRITE
10 ENABLE ON QUERY COMPUTATION
11 AS
12 SELECT count(*) as contador,
13 receivable.dueDate as finalDate,
14 schedule.id as scheduleId,
15 receivable.recvChargeTypeId as recvChargeTypeId ,
16 receivable.scheduleId as recScheduleId
17 FROM ALFAODS.OdsReceivable receivable
18 INNER JOIN ALFAODS.OdsChargeType chargeType on receivable.recvChargeTypeId = chargeType.id
19 INNER JOIN ALFAODS.OdsScheduleMain schedule on receivable.scheduleId = schedule.id
20 where
21 receivable.amount NOT IN (0.01, 0.00)
22 AND chargeType.code = 2
23 group by receivable.dueDate , schedule.id , receivable.recvChargeTypeId, receivable.scheduleId
24 ;
Materialized view created.
Elapsed: 00:00:21.94
SQL>
SQL> create index alfaods.idx_mv_max_fn_date on alfaods.mv_max_fn_date ( scheduleId ) nologging nocompress tablespace tbdata ;
Index created.
Elapsed: 00:00:04.13
SQL>
SQL> exec dbms_stats.gather_table_stats('ALFAODS','MV_MAX_FN_DATE', method_opt=> 'FOR ALL COLUMNS SIZE AUTO', cascade => true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.36
SQL>
SQL> exec dbms_mview.refresh ('ALFAODS.MV_MAX_FN_DATE','F');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.46
SQL>