12.2 Introduction to Real-Time Materialized Views (The View)

Although I usually focus on index related topics, I’ve always kinda considered Materialized Views (MVs) as an index like structure, which Oracle can automatically update and from which Oracle can efficiently retrieve data. The cost of maintaining a Materialized View Log is not unlike the cost of maintaining an index structure, the benefits of which can potentially far outweigh the overheads.

I just want to introduce a really cool new feature introduced in Oracle Database 12c Release 2 called Real-Time Materialized Views.

To best illustrate, a simple little demo. I first create a table and populate it with 1M rows.

SQL> create table bowie (id number primary key, name varchar2(42), sales number, text varchar2(42));

Table created.

SQL> insert into bowie select rownum, 'BOWIE' || to_char(mod(rownum,100)+1),
trunc(dbms_random.value(0,10000)), 'ZIGGY STARDUST' from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

I then run the following query which returns only those summary records where the total SALES exceeds some limit:

SQL> select name, sum(sales) from bowie group by name having sum(sales) > 50500000;

NAME                                       SUM(SALES)
------------------------------------------ ----------
BOWIE7                                       50570391
BOWIE55                                      50586083
BOWIE15                                      50636084

Execution Plan
----------------------------------------------------------
Plan hash value: 298288086

-----------------------------------------------------------------------------
| Id | Operation         | Name  | Rows  | Bytes | Cost (%CPU) | Time       |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |       |    50 |   600 |    1454 (4) | 00:00:01   |
|* 1 | FILTER            |       |       |       |             |            |
|  2 | HASH GROUP BY     |       |    50 |   600 |    1454 (4) | 00:00:01   |
|  3 | TABLE ACCESS FULL | BOWIE | 1000K |   11M |    1410 (1) | 00:00:01   |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SUM("SALES")>50500000)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
5138 consistent gets
   0 physical reads
   0 redo size
 704 bytes sent via SQL*Net to client
 608 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   3 rows processed

I don’t have any filtering predicates before the summarisation of the table, meaning I’m currently forced to read the entire table first, before I can filter any summarisations that aren’t of interest.

As such, a full table scan is an expensive operation here (5138 consistent gets).

Now a method to reduce these FTS overheads is to create a Materialized View which has all the summary details pre-defined. Depending on the QUERY_REWRITE_INTEGRITY parameter, I can potentially use Query Rewrite to automatically use the MV to access the pre-summarised data rather than perform the FTS on the base Bowie table.

The MV could be kept fully up to date by performing a FAST REFRESH ON COMMIT but this adds additional overheads on the DMLs on the base table as they have to apply the actual changes to the MVs as part of the transaction. I could reduce these overheads by performing a FAST REFRESH ON DEMAND, but this means the MV may be stale and not fully up to date.

In Oracle Database 12.2, we get the best of both worlds with Real-Time Materialized Views, where we don’t have the additional overheads of a ON COMMIT refresh, but still guarantee fully up to date data by still (hopefully) accessing the MV rather than performing the expensive FTS.

We first create the Materialized View Log (necessary for MV fast refreshes):

SQL> create materialized view log on bowie with sequence, rowid (id, name, sales) including new values;

Materialized view log created.

But now create the MV with the required summary SQL definition, but with the new ENABLE ON QUERY COMPUTATION clause:

SQL> create materialized view bowie_mv
2 refresh fast on demand
3 enable query rewrite
4 enable on query computation
5 as
6 select name, sum(sales) from bowie group by name;

Materialized view created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_MV');

PL/SQL procedure successfully completed.

If we re-run the summary query again:

SQL> select name, sum(sales) from bowie group by name having sum(sales) > 50500000;

NAME                                       SUM(SALES)
------------------------------------------ ----------
BOWIE7                                       50570391
BOWIE55                                      50586083
BOWIE15                                      50636084

Execution Plan
----------------------------------------------------------
Plan hash value: 593592962

-----------------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |   10 |   140 |       3 (0) | 00:00:01  |
|* 1 | MAT_VIEW REWRITE ACCESS FULL | BOWIE_MV |   10 |   140 |       3 (0) | 00:00:01  |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("BOWIE_MV"."SUM(SALES)">50500000)

Statistics
----------------------------------------------------------
  0 recursive calls
  2 db block gets
  8 consistent gets
  0 physical reads
  0 redo size
704 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  3 rows processed

We notice that Query Rewrite has taken place and the CBO has automatically used the MV (consisting of just 100 rows) to very efficiently access the required summary data (8 consistent gets).

If we look at the current Query Rewrite parameters:

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_query                       string      ENABLE
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

We notice that QUERY_REWRITE_INTEGRITY is set to ENFORCED meaning that Oracle enforces and guarantees consistency and integrity. So no stale accesses to the MV will be tolerated here.

If we now add and commit a new row (that effectively adds 1000 to the BOWIE7 summary):

SQL> insert into bowie values (1000001, 'BOWIE7', 1000, 'HUNKY DORY');

1 row created.

SQL> commit;

Commit complete.

And now again re-run the summary query:

SQL> select name, sum(sales) from bowie group by name having sum(sales) > 50500000;

NAME                                       SUM(SALES)
------------------------------------------ ----------
BOWIE55                                      50586083
BOWIE15                                      50636084
BOWIE7                                       50571391

We notice the returned data is fully up to date (the total for BOWIE7 has indeed increased by the 1000 added).

And it did so efficiently without having to perform a massive FTS on the base table. A look at the execution plan reveals how:

Execution Plan
----------------------------------------------------------
Plan hash value: 3454774452

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name                       | Rows | Bytes | Cost (%CPU)|     Time |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                            |  203 |  7308 |     22 (28)| 00:00:01 |
|   1 |  VIEW                                     |                            |  203 |  7308 |     22 (28)| 00:00:01 |
|   2 |   UNION-ALL                               |                            |      |       |            |          |
| * 3 |    VIEW                                   | VW_FOJ_0                   |  100 |  3900 |      8 (25)| 00:00:01 |
| * 4 |     HASH JOIN OUTER                       |                            |  100 |  2500 |      8 (25)| 00:00:01 |
|   5 |      VIEW                                 |                            |  100 |  1700 |       3 (0)| 00:00:01 |
|   6 |       MAT_VIEW ACCESS FULL                | BOWIE_MV                   |  100 |  1400 |       3 (0)| 00:00:01 |
|   7 |      VIEW                                 |                            |    1 |     8 |      5 (40)| 00:00:01 |
|   8 |       HASH GROUP BY                       |                            |    1 |    36 |      5 (40)| 00:00:01 |
|   9 |        VIEW                               |                            |    1 |    36 |      4 (25)| 00:00:01 |
|  10 |         RESULT CACHE                      | csfyggq82gxrn757upr194x2g2 |      |       |            |          |
|* 11 |          VIEW                             |                            |    1 |   100 |      4 (25)| 00:00:01 |
|  12 |           WINDOW SORT                     |                            |    1 |   191 |      4 (25)| 00:00:01 |
|* 13 |            TABLE ACCESS FULL              | MLOG$_BOWIE                |    1 |   191 |       3 (0)| 00:00:01 |
|* 14 |   VIEW                                    | VW_FOJ_1                   |  102 |  5304 |      8 (25)| 00:00:01 |
|* 15 |    HASH JOIN FULL OUTER                   |                            |  102 |  3774 |      8 (25)| 00:00:01 |
|  16 |     VIEW                                  |                            |    1 |    30 |      5 (40)| 00:00:01 |
|  17 |      HASH GROUP BY                        |                            |    1 |    36 |      5 (40)| 00:00:01 |
|  18 |       VIEW                                |                            |    1 |    36 |      4 (25)| 00:00:01 |
|  19 |        RESULT CACHE                       | csfyggq82gxrn757upr194x2g2 |      |       |            |          |
|* 20 |         VIEW                              |                            |    1 |   100 |      4 (25)| 00:00:01 |
|  21 |          WINDOW SORT                      |                            |    1 |   191 |      4 (25)| 00:00:01 |
|* 22 |           TABLE ACCESS FULL               | MLOG$_BOWIE                |    1 |   191 |       3 (0)| 00:00:01 |
|  23 |   VIEW                                    |                            |  100 |   700 |       3 (0)| 00:00:01 |
|  24 |    MAT_VIEW ACCESS FULL                   | BOWIE_MV                   |  100 |  1400 |       3 (0)| 00:00:01 |
|  25 | NESTED LOOPS                              |                            |    1 |    75 |      6 (34)| 00:00:01 |
|  26 |  VIEW                                     |                            |    1 |    52 |      5 (40)| 00:00:01 |
|  27 |   HASH GROUP BY                           |                            |    1 |    36 |      5 (40)| 00:00:01 |
|  28 |    VIEW                                   |                            |    1 |    36 |      4 (25)| 00:00:01 |
|  29 |     RESULT CACHE                          | csfyggq82gxrn757upr194x2g2 |      |       |            |          |
|* 30 |      VIEW                                 |                            |    1 |   100 |      4 (25)| 00:00:01 |
|  31 |       WINDOW SORT                         |                            |    1 |   191 |      4 (25)| 00:00:01 |
|* 32 |        TABLE ACCESS FULL                  | MLOG$_BOWIE                |    1 |   191 |       3 (0)| 00:00:01 |
|* 33 |  MAT_VIEW ACCESS BY INDEX ROWID           | BOWIE_MV                   |    1 |    23 |       1 (0)| 00:00:01 |
|* 34 |   INDEX UNIQUE SCAN                       | I_SNAP$_BOWIE_MV           |    1 |       |       0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("AV$0"."OJ_MARK" IS NULL AND "SNA$0"."SUM(SALES)">50500000)
4 - access(SYS_OP_MAP_NONNULL("SNA$0"."NAME")=SYS_OP_MAP_NONNULL("AV$0"."GB0"(+)))
11 - filter(SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('6E00') AND
"MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR (SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=H
EXTORAW('6F00') OR SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('7500'))
AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
13 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-03-31 15:47:28', 'syyyy-mm-dd hh24:mi:ss'))
14 - filter("SNA$0"."SNA_OJ_MARK" IS NULL AND DECODE("AV$0"."H0",0,TO_NUMBER(NULL),"AV$0"."D0")>50500000)
15 - access(SYS_OP_MAP_NONNULL("SNA$0"."NAME")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
20 - filter(SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('6E00') AND
"MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR (SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=H
EXTORAW('6F00') OR SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('7500'))
AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
22 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-03-31 15:47:28', 'syyyy-mm-dd hh24:mi:ss'))
30 - filter(SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('6E00') AND
"MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR (SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=H
EXTORAW('6F00') OR SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('7500'))
AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
32 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-03-31 15:47:28', 'syyyy-mm-dd hh24:mi:ss'))
33 - filter(DECODE(TO_CHAR("BOWIE_MV"."SUM(SALES)"),NULL,DECODE("AV$0"."H0",0,TO_NUMBER(NULL),"AV$0"."
D0"),"BOWIE_MV"."SUM(SALES)"+"AV$0"."D0")>50500000)
34 - access(SYS_OP_MAP_NONNULL("NAME")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

Result Cache Information (identified by operation id):
------------------------------------------------------

10 - column-count=7; dependencies=(BOWIE.MLOG$_BOWIE); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_BOWIE"
19 - column-count=7; dependencies=(BOWIE.MLOG$_BOWIE); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_BOWIE"
29 - column-count=7; dependencies=(BOWIE.MLOG$_BOWIE); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_BOWIE"

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan

Statistics
----------------------------------------------------------
  0 recursive calls
  4 db block gets
 16 consistent gets
  0 physical reads
  0 redo size
704 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  3 rows processed

Oracle can now merge the data from the MV with the data still within the MV Log to generate the final, fully up to date result. At just 16 consistent gets, this is more expensive than the fully refreshed MV (8 consistent gets) but much less than the 5138 consistent gets when accessing the base BOWIE table via a FTS.

And providing the costs of doing so is calculated as less by the CBO than performing the FTS (or otherwise) on the base table, then Oracle will perform this new smart when accessing data from such created MVs.

Very nice 🙂