Cannot Set ON COMMIT refresh attribute for Materialized View with PIVOT clause

Question

I am trying to create the following Materialized View but have gotten this error:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

I'm using a complete refresh on commit because the table referenced in the view will only have a handful of commits a few times a month and the updates will only be for a few records. Our team has decided complete refresh on commit is the way to go.

Here is the DDL:

CREATE MATERIALIZED VIEW TBLEDW_UANDC_PIVOT
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
ENABLE QUERY REWRITE
  AS
SELECT * FROM 
  (SELECT DISTINCT UC.MAC_CD, UC.GCN_INDEX, UC.GCN_CD, UC.PKG_SZ, UC.U_C_RATE, UC.U_C_AMT FROM 
DMT.TBLEDW_UANDC UC) 
PIVOT 
(SUM(U_C_AMT) FOR (U_C_RATE) IN (10 AS UCA, 30 AS UCB, 50 AS UCC, 70 AS UCD, 90 AS UCE));

I can't figure out why this error is occurring, thank you for any insight!

Can you create a REFRESH ON COMMIT materialized view without the PIVOT clause? I ask because I'm wondering if you have the necessary privileges. — Bob Jarvis - Reinstate Monica
@BobJarvis-ReinstateMonica I do need to use the PIVOT clause, and this script is being administered by a DBA with privileges. — karen blanco
Do you have the ON COMMIT REFRESH privilege? Is DMT.TBLEDW_UANDC a view based on a remote table? — Wernfried Domscheit
@WernfriedDomscheit my DBA who is running the script has privileges, but yes, this is based on a remote table. — karen blanco

Connor McDonald Connor McDonald · Accepted Answer · 2020-01-24T03:01:41

Here is an example of the PIVOT not working and how to work around it with DECODE.

Full commentary around the example at my blog https://connor-mcdonald.com/2019/10/02/a-refreshing-look-at-pivot/

SQL> create table patient
  2   ( region      int,
  3     office       int,
  4     patient      int,
  5     some_date date );

Table created.

SQL>
SQL> alter table patient add primary key ( region, office, patient );

Table altered.

SQL>
SQL> insert into patient values (1,1,1,sysdate);

1 row created.

SQL> insert into patient values (1,1,2,sysdate);

1 row created.

SQL> insert into patient values (1,1,3,sysdate);

1 row created.

SQL>
SQL> create table patient_attrib
  2   ( region      int,
  3     office       int,
  4     patient      int,
  5     property      varchar2(10),
  6     val           number);

Table created.

SQL>
SQL>
SQL> alter table patient_attrib add primary key ( region, office, patient, property );

Table altered.

SQL> alter table patient_attrib add constraint patient_attrib_fk
  2  foreign key ( region,office,patient) references patient (region,office,patient);

Table altered.

SQL>
SQL> insert into patient_attrib values (1,1,2,'weight',60);

1 row created.

SQL> insert into patient_attrib values (1,1,2,'height',1);

1 row created.

SQL> insert into patient_attrib values (1,1,2,'bp',2);

1 row created.

SQL> insert into patient_attrib values (1,1,2,'heart',3);

1 row created.

SQL> insert into patient_attrib values (1,1,2,'chol',4);

1 row created.

SQL> insert into patient_attrib values (1,1,2,'fatpct',5);

1 row created.

SQL>
SQL> insert into patient_attrib values (1,1,3,'weight',61);

1 row created.

SQL> insert into patient_attrib values (1,1,3,'height',1.1);

1 row created.

SQL> insert into patient_attrib values (1,1,3,'bp',2.1);

1 row created.

SQL> insert into patient_attrib values (1,1,3,'heart',3.1);

1 row created.

SQL> insert into patient_attrib values (1,1,3,'chol',4.1);

1 row created.

SQL> insert into patient_attrib values (1,1,3,'fatpct',5.1);

1 row created.

SQL> select region, office, patient, some_date, weight_val, height_val, bp_val, heart_val, chol_val, fatpct_val
  2  from
  3  (
  4  select h.*, hs.property, hs.val
  5  from   patient h,
  6         patient_attrib hs
  7  where  h.region = hs.region
  8  and    h.office  = hs.office
  9  and    h.patient = hs.patient
 10  )
 11  pivot ( sum(val) as val for ( property ) in ('weight' as weight, 'height' as height, 
 12       'bp' as bp, 'heart' as heart, 'chol' as chol, 'fatpct' as fatpct));

    REGION     OFFICE    PATIENT SOME_DATE WEIGHT_VAL HEIGHT_VAL     BP_VAL  HEART_VAL   CHOL_VAL FATPCT_VAL
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          2 02-OCT-19         60          1          2          3          4          5
         1          1          3 02-OCT-19         61        1.1        2.1        3.1        4.1        5.1

SQL> create materialized view log on patient
  2    with sequence, rowid (region,office,patient,some_date) including new values
  3  /

Materialized view log created.

SQL>
SQL> create materialized view log on patient_attrib
  2    with sequence, rowid (region,office,patient,property, val) including new values
  3  /

Materialized view log created.

SQL> create materialized view MV
  2  refresh fast
  3  -- on commit
  4  as
  5  select region, office, patient, some_date, weight_val, height_val, bp_val, heart_val, chol_Val, fatpct_val
  6  from
  7  (
  8  select h.*, hs.property, hs.val
  9  from   patient h,
 10         patient_attrib hs
 11  where  h.region = hs.region
 12  and    h.office  = hs.office
 13  and    h.patient = hs.patient
 14  )
 15  pivot ( sum(val) as val for ( property ) in ('weight' as weight, 'height' as height, 
 16          'bp' as bp, 'heart' as heart, 'chol' as chol, 'fatpct' as fatpct));
create materialized view MV
*
ERROR at line 1:
ORA-12015: cannot create a fast refresh materialized view from a complex query

SQL> select h.region, h.office, h.patient, h.some_date,
  2         sum(decode(hs.property, 'weight', hs.val, 0)) weight_val,
  3         sum(decode(hs.property, 'height', hs.val, 0)) height_val,
  4         sum(decode(hs.property, 'bp', hs.val, 0)) bp_val,
  5         sum(decode(hs.property, 'heart', hs.val, 0)) heart_val,
  6         sum(decode(hs.property, 'chol', hs.val, 0)) chol_Val,
  7         sum(decode(hs.property, 'fatpct', hs.val, 0)) fatpct_val
  8  from   patient h,
  9         patient_attrib hs
 10  where  h.region = hs.region
 11  and    h.office  = hs.office
 12  and    h.patient = hs.patient
 13  group by h.region, h.office, h.patient, h.some_date;

    REGION     OFFICE    PATIENT SOME_DATE WEIGHT_VAL HEIGHT_VAL     BP_VAL  HEART_VAL   CHOL_VAL FATPCT_VAL
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          2 02-OCT-19         60          1          2          3          4          5
         1          1          3 02-OCT-19         61        1.1        2.1        3.1        4.1        5.1

2 rows selected.

SQL> create materialized view MV
  2  refresh fast
  3  on commit
  4  as
  5  select h.region, h.office, h.patient, h.some_date,
  6         count(*) c,
  7         count(decode(hs.property, 'weight', hs.val, 0)) weight_cnt,
  8         count(decode(hs.property, 'height', hs.val, 0)) height_cnt,
  9         count(decode(hs.property, 'bp', hs.val, 0)) bp_cnt,
 10         count(decode(hs.property, 'heart', hs.val, 0)) heart_cnt,
 11         count(decode(hs.property, 'chol', hs.val, 0)) chol_cnt,
 12         count(decode(hs.property, 'fatpct', hs.val, 0)) fatpct_cnt,
 13         sum(decode(hs.property, 'weight', hs.val, 0)) weight_val,
 14         sum(decode(hs.property, 'height', hs.val, 0)) height_val,
 15         sum(decode(hs.property, 'bp', hs.val, 0)) bp_val,
 16         sum(decode(hs.property, 'heart', hs.val, 0)) heart_val,
 17         sum(decode(hs.property, 'chol', hs.val, 0)) chol_Val,
 18         sum(decode(hs.property, 'fatpct', hs.val, 0)) fatpct_val
 19  from   patient h,
 20         patient_attrib hs
 21  where  h.region = hs.region
 22  and    h.office  = hs.office
 23  and    h.patient = hs.patient
 24  group by h.region, h.office, h.patient, h.some_date;

Materialized view created.

Cannot Set ON COMMIT refresh attribute for Materialized View with PIVOT clause

3 Answers