Search

Top 60 Oracle Blogs

Recent comments

A refreshing look at PIVOT

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”  Smile

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>