oracle12c - Oracle Materialized View with ENABLE ON QUERY COMPUTATION

Question

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)

question from:https://stackoverflow.com/questions/65886023/oracle-materialized-view-with-enable-on-query-computation

Answer 1

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>