Posted by We had an AskTOM question come in recently where our customer was very excited about the PIVOT operator in SQL which lets you transpose rows to columns. This is a very common requirement in applications that want to take data that has been modelled in “pure” relational form, and present in a more “human-digestible” form. There are plenty of posts and examples out there about PIVOT, but if you haven’t seen one, here’s a trivial example just so that you are up to speed for the rest of this post.
SQL> select deptno, sum(sal) tot_sal
2 from scott.emp
3 group by deptno
4 order by 1;
DEPTNO TOT_SAL
---------- ----------
10 8750
20 10875
30 9400
3 rows selected.
--
-- which we can flip into a single row with three columns
--
SQL>
SQL> select *
2 from (select deptno, sal
3 from scott.emp)
4 pivot (sum(sal) as tot_sal for (deptno) in (10 as dept10, 20 as dept20, 30 as dept30));
DEPT10_TOT_SAL DEPT20_TOT_SAL DEPT30_TOT_SAL
-------------- -------------- --------------
8750 10875 9400
More on PIVOT here in the docs.
Like any form of data aggregation, there is a cost is doing a PIVOT and our customer wanted to offset that cost by putting the results into a materialized view. And as the saying goes… “That is when the fight started” 
For the purpose of demonstration, let’s assume we run a medical practice and we capture information about doctor’s offices and their patient. Here is my rudimentary data model with some sample data:
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.
We have patients and various measurements about those patients. For reporting purposes, we want to output the patient records in a pivoted style, and hence the PIVOT operator is a natural fit:
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
2 rows selected.
As mentioned, as the patient volume increases, we’ll seek to make that output come from a fast refreshable materialized view. To support that, we’ll throw in some materialized view logs in the normal way
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.
Now I’ll take my existing PIVOT query and use that as the source for my materialized view
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
Unfortunately for us, PIVOT is a no-go for a fast refresh materialized view. Even if I try to utilize the precreated table “trick” which sometimes can work around this issue, we’re still stuck.
SQL> create table MV as
2 select region, office, patient, some_date, weight_val, height_val, bp_val, heart_val, chol_Val, fatpct_val
3 from
4 (
5 select h.*, hs.property, hs.val
6 from patient h,
7 patient_attrib hs
8 where h.region = hs.region
9 and h.office = hs.office
10 and h.patient = hs.patient
11 )
12 pivot ( sum(val) as val for ( property ) in ('weight' as weight, 'height' as height,
13 'bp' as bp, 'heart' as heart, 'chol' as chol, 'fatpct' as fatpct));
Table created.
SQL> create materialized view MV
2 on prebuilt table
3 refresh fast 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-12054: cannot set the ON COMMIT refresh attribute for the materialized view
But all is not lost. Before PIVOT arrived in 11g, SQL practitioners had other mechanisms for achieving the same result, albeit with a more verbose and unwieldy syntax, via DECODE:
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.
Armed with this, and knowing that we need some additional aggregates for fast refresh on commit materialized views, we can achieve our desired result.
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.
Now we can test out the refresh capabilities of the view with some standard DML
SQL> select region, office, patient, some_date, weight_val, height_val, bp_val, heart_val, chol_Val,fatpct_val
2 from mv order by 1,2,3;
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> insert into patient values (1,1,4,sysdate);
1 row created.
SQL>
SQL> insert into patient_attrib values (1,1,4,'weight',62);
1 row created.
SQL> insert into patient_attrib values (1,1,4,'height',1.2);
1 row created.
SQL> insert into patient_attrib values (1,1,4,'bp',2.2);
1 row created.
SQL> insert into patient_attrib values (1,1,4,'heart',3.2);
1 row created.
SQL> insert into patient_attrib values (1,1,4,'chol',4.2);
1 row created.
SQL> insert into patient_attrib values (1,1,4,'fatpct',5.2);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select region, office, patient, some_date, weight_val, height_val, bp_val, heart_val, chol_Val,fatpct_val
2 from mv order by 1,2,3;
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
1 1 4 02-OCT-19 62 1.2 2.2 3.2 4.2 5.2
3 rows selected.
SQL>
SQL> update patient_attrib
2 set val = 65
3 where patient = 3
4 and property = 'weight';
1 row updated.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select region, office, patient, some_date, weight_val, height_val, bp_val, heart_val, chol_Val,fatpct_val
2 from mv order by 1,2,3;
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 65 1.1 2.1 3.1 4.1 5.1
1 1 4 02-OCT-19 62 1.2 2.2 3.2 4.2 5.2
3 rows selected.
and like any materialized view, we can get a report on all of the capabilities available to us via DBMS_MVIEW
SQL> @?/rdbms/admin/utlxmv.sql
Table created.
SQL> delete mv_capabilities_table;
0 rows deleted.
SQL> EXEC dbms_mview.explain_mview('MV');
PL/SQL procedure successfully completed.
SQL> select * from mv_capabilities_table
2 @pr
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 1
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REFRESH_COMPLETE
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 1002
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REFRESH_FAST
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 2003
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REWRITE
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 3004
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : PCT_TABLE
POSSIBLE : N
RELATED_TEXT : PATIENT
RELATED_NUM : 840
MSGNO : 2068
MSGTXT : relation is not a partitioned table
SEQ : 4005
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : PCT_TABLE
POSSIBLE : N
RELATED_TEXT : PATIENT_ATTRIB
RELATED_NUM : 858
MSGNO : 2068
MSGTXT : relation is not a partitioned table
SEQ : 4006
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_INSERT
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 5007
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ONETAB_DML
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 6008
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REFRESH_FAST_AFTER_ANY_DML
POSSIBLE : Y
RELATED_TEXT :
RELATED_NUM :
MSGNO :
MSGTXT :
SEQ : 7009
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REFRESH_FAST_PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2157
MSGTXT : PCT is not possible on any of the detail tables in the materialized view
SEQ : 8010
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REWRITE_FULL_TEXT_MATCH
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 9011
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REWRITE_PARTIAL_TEXT_MATCH
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 10012
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REWRITE_GENERAL
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2159
MSGTXT : query rewrite is disabled on the materialized view
SEQ : 11013
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : REWRITE_PCT
POSSIBLE : N
RELATED_TEXT :
RELATED_NUM :
MSGNO : 2158
MSGTXT : general rewrite is not possible or PCT is not possible on any of the detail tables
SEQ : 12014
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : PCT_TABLE_REWRITE
POSSIBLE : N
RELATED_TEXT : PATIENT
RELATED_NUM : 840
MSGNO : 2068
MSGTXT : relation is not a partitioned table
SEQ : 13015
==============================
STATEMENT_ID :
MVOWNER : MCDONAC
MVNAME : MV
CAPABILITY_NAME : PCT_TABLE_REWRITE
POSSIBLE : N
RELATED_TEXT : PATIENT_ATTRIB
RELATED_NUM : 858
MSGNO : 2068
MSGTXT : relation is not a partitioned table
SEQ : 13016
PL/SQL procedure successfully completed.
SQL>
SQL>
Learning is not a spectator sport 
Hmm, interesting.
Especially since Oracle internally rewrites a pivot-select into another select for achieving the same result, albeit with a more verbose and unwieldy syntax:
declare l_clob clob;
BEGIN
DBMS_UTILITY.expand_sql_text (
input_sql_text => q'|
select region, office, patient, some_date, weight_val, height_val, bp_val, heart_val, chol_val, fatpct_val
from
(
select h.*, hs.property, hs.val
from patient h,
patient_attrib hs
where h.region = hs.region
and h.office = hs.office
and h.patient = hs.patient
)
pivot ( sum(val) as val for ( property ) in ('weight' as weight, 'height' as height,
'bp' as bp, 'heart' as heart, 'chol' as chol, 'fatpct' as fatpct))
|',
output_sql_text => l_clob
);
dbms_output.put_line(l_clob);
END;
Statement processed.
SELECT "A1"."REGION" "REGION","A1"."OFFICE" "OFFICE","A1"."PATIENT" "PATIENT","A1"."SOME_DATE" "SOME_DATE","A1"."WEIGHT_VAL" "WEIGHT_VAL","A1"."HEIGHT_VAL" "HEIGHT_VAL","A1"."BP_VAL" "BP_VAL","A1"."HEART_VAL" "HEART_VAL","A1"."CHOL_VAL" "CHOL_VAL","A1"."FATPCT_VAL" "FATPCT_VAL" FROM (SELECT "A2"."REGION" "REGION","A2"."OFFICE" "OFFICE","A2"."PATIENT" "PATIENT","A2"."SOME_DATE" "SOME_DATE",SUM(CASE WHEN ("A2"."PROPERTY"='weight') THEN "A2"."VAL" END ) "WEIGHT_VAL",SUM(CASE WHEN ("A2"."PROPERTY"='height') THEN "A2"."VAL" END ) "HEIGHT_VAL",SUM(CASE WHEN ("A2"."PROPERTY"='bp') THEN "A2"."VAL" END ) "BP_VAL",SUM(CASE WHEN ("A2"."PROPERTY"='heart') THEN "A2"."VAL" END ) "HEART_VAL",SUM(CASE WHEN ("A2"."PROPERTY"='chol') THEN "A2"."VAL" END ) "CHOL_VAL",SUM(CASE WHEN ("A2"."PROPERTY"='fatpct') THEN "A2"."VAL" END ) "FATPCT_VAL" FROM (SELECT "A4"."REGION" "REGION","A4"."OFFICE" "OFFICE","A4"."PATIENT" "PATIENT","A4"."SOME_DATE" "SOME_DATE","A3"."PROPERTY" "PROPERTY","A3"."VAL" "VAL" FROM "SQL_QMGSXSQMWREMDRTBBAMAPOPSC"."PATIENT" "A4","SQL_QMGSXSQMWREMDRTBBAMAPOPSC"."PATIENT_ATTRIB" "A3" WHERE "A4"."REGION"="A3"."REGION" AND "A4"."OFFICE"="A3"."OFFICE" AND "A4"."PATIENT"="A3"."PATIENT") "A2" GROUP BY "A2"."REGION","A2"."OFFICE","A2"."PATIENT","A2"."SOME_DATE") "A1"