sql - 无法使用 PIVOT 子句为实体化 View 设置 ON COMMIT 刷新属性

Question

我正在尝试创建以下物化 View ，但出现此错误:

ORA-12054: 无法为物化 View 设置 ON COMMIT 刷新属性

我在提交时使用完全刷新，因为 View 中引用的表每个月只会有几次提交，而且更新只会针对少数记录。我们的团队已经决定在提交时完全刷新是要走的路。

这是 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));

我不知道为什么会出现这个错误，感谢您的任何见解!

Answer 1

这是一个 PIVOT 不工作的例子，以及如何使用 DECODE 解决它。

在我的博客上对示例进行了完整评论 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.