8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)

Related articles.

Introduction

Materialized views are a really useful performance feature, allowing you to pre-calcuate joins and aggregations, which can make applications and reports feel more responsive. The complication comes from the lag between the last refresh of the materialized view and subsequent DML changes to the base tables. Fast refreshes allow you to run refreshes more often, and in some cases you can make use of refreshes triggered on commit of changes to the base tables, but this can represent a significant overhead from a DML performance perspective.

Oracle 12.2 introduced the concept of real-time materialized views, which allow a statement-level wind-forward of a stale materialised view, making the data appear fresh to the statement. This wind-forward is based on changes computed using materialized view logs, similar to a conventional fast refresh, but the operation only affect the current statement. The changes are not persisted in the materialized view, so a conventional refresh is still required at some point.

Real Time Materialized View

The real-time materialized functionality has some restrictions associated with it including the following.

The rest of this article provides some simple examples of real-time materialized views.

Setup

We need a table to act as the source of the materialized view. The following script creates and populates a test table with random data.

CONN test/test@pdb1

DROP TABLE order_lines PURGE;

CREATE TABLE order_lines (
   id            NUMBER(10),
   order_id      NUMBER(10),
   line_qty      NUMBER(5),
   total_value   NUMBER(10,2),
   created_date DATE,
   CONSTRAINT orders_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO order_lines
SELECT level AS id,
       TRUNC(DBMS_RANDOM.value(1,1000)) AS order_id,
       TRUNC(DBMS_RANDOM.value(1,20)) AS line_qty,
       ROUND(DBMS_RANDOM.value(1,1000),2) AS total_value,
       TRUNC(SYSDATE - DBMS_RANDOM.value(0,366)) AS created_date
FROM   dual CONNECT BY level <= 100000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'order_lines');

Materialized View Logs

For real-time materialized views to work we must have materialised view logs on all the tables the materialized view is based on.

DROP MATERIALIZED VIEW LOG ON order_lines;

CREATE MATERIALIZED VIEW LOG ON order_lines
WITH ROWID, SEQUENCE(order_id, line_qty, total_value)
INCLUDING NEW VALUES;

Materialized View

We can now create the materialized view. Notice the ENABLE ON QUERY COMPUTATION option, which is new to Oracle 12.2.

DROP MATERIALIZED VIEW order_summary_rtmv;

CREATE MATERIALIZED VIEW order_summary_rtmv
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
SELECT order_id,
       SUM(line_qty) AS sum_line_qty,
       SUM(total_value) AS sum_total_value,
       COUNT(*) AS row_count
FROM   order_lines
GROUP BY order_id;

EXEC DBMS_STATS.gather_table_stats(USER, 'order_summary_rtmv');

Basic Rewrite

As with previous versions of the database, if we run a query that could be serviced quicker by the materialized view, Oracle will rewrite the query to use the materialized view.

SELECT order_id,
       SUM(line_qty) AS sum_line_qty,
       SUM(total_value) AS sum_total_value,
       COUNT(*) AS row_count
FROM   order_lines
WHERE  order_id = 1
GROUP BY order_id;

  ORDER_ID SUM_LINE_QTY SUM_TOTAL_VALUE  ROW_COUNT
---------- ------------ --------------- ----------
	 1	    880        44573.88 	95

SQL>


SET LINESIZE 200 PAGESIZE 100
SELECT *
FROM   dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID	3rttkdd0ybtaw, child number 0
-------------------------------------
SELECT order_id,	SUM(line_qty) AS sum_line_qty,
SUM(total_value) AS sum_total_value,	    COUNT(*) AS row_count FROM
 order_lines WHERE  order_id = 1 GROUP BY order_id

Plan hash value: 1165901663

---------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name		  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |			  |	  |	  |	4 (100)|	  |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV |	1 |    17 |	4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   1 - filter("ORDER_SUMMARY_RTMV"."ORDER_ID"=1)


20 rows selected.

SQL>

We can see from the execution plan the materialized view was used, rather than accessing the base table. Notice the row count value of 95.

Rewrite Plus Real-Time Refresh

We amend the data in the table, so the materialized view is now considered stale.

INSERT INTO order_lines VALUES (100001, 1, 30, 10000, SYSDATE);
COMMIT;

COLUMN mview_name FORMAT A30

SELECT mview_name,
       staleness,
       on_query_computation
FROM   user_mviews;

MVIEW_NAME		       STALENESS	   O
------------------------------ ------------------- -
ORDER_SUMMARY_RTMV	       NEEDS_COMPILE	   Y

SQL>

A regular materialized view would no longer be considered for query rewrites unless we had the QUERY_REWRITE_INTEGRITY parameter set to STALE_TOLERATED for the session. Since we have the ENABLE ON QUERY COMPUTATION option on the materialized view it is still considered usable, as Oracle will dynamically amend the values to reflect the changes in the materialized view logs.

SELECT order_id,
       SUM(line_qty) AS sum_line_qty,
       SUM(total_value) AS sum_total_value,
       COUNT(*) AS row_count
FROM   order_lines
WHERE  order_id = 1
GROUP BY order_id;

  ORDER_ID SUM_LINE_QTY SUM_TOTAL_VALUE  ROW_COUNT
---------- ------------ --------------- ----------
	 1	    910        54573.88 	96

SQL>


SET LINESIZE 200 PAGESIZE 100
SELECT *
FROM   dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID	3rttkdd0ybtaw, child number 1
-------------------------------------
SELECT order_id,	SUM(line_qty) AS sum_line_qty,
SUM(total_value) AS sum_total_value,	    COUNT(*) AS row_count FROM
 order_lines WHERE  order_id = 1 GROUP BY order_id

Plan hash value: 1640379716

------------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name			 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |				 |	 |	 |    19 (100)| 	 |
|   1 |  VIEW				    |				 |  1001 | 52052 |    19  (27)| 00:00:01 |
|   2 |   UNION-ALL			    |				 |	 |	 |	      | 	 |
|*  3 |    FILTER			    |				 |	 |	 |	      | 	 |
|*  4 |     HASH JOIN OUTER		    |				 |     1 |    33 |     9  (23)| 00:00:01 |
|*  5 |      MAT_VIEW ACCESS FULL	    | ORDER_SUMMARY_RTMV	 |     1 |    17 |     4   (0)| 00:00:01 |
|   6 |      VIEW			    |				 |     1 |    16 |     5  (40)| 00:00:01 |
|   7 |       HASH GROUP BY		    |				 |     1 |    39 |     5  (40)| 00:00:01 |
|   8 |        VIEW			    |				 |     1 |    39 |     4  (25)| 00:00:01 |
|   9 | 	RESULT CACHE		    | dyqrs00u554qffvsw6akf32p2p |	 |	 |	      | 	 |
|* 10 | 	 VIEW			    |				 |     1 |   103 |     4  (25)| 00:00:01 |
|  11 | 	  WINDOW SORT		    |				 |     1 |   194 |     4  (25)| 00:00:01 |
|* 12 | 	   TABLE ACCESS FULL	    | MLOG$_ORDER_LINES 	 |     1 |   194 |     3   (0)| 00:00:01 |
|  13 |    VIEW 			    |				 |  1000 | 52000 |    10  (30)| 00:00:01 |
|  14 |     UNION-ALL			    |				 |	 |	 |	      | 	 |
|* 15 |      FILTER			    |				 |	 |	 |	      | 	 |
|  16 |       NESTED LOOPS OUTER	    |				 |   999 | 94905 |     4  (25)| 00:00:01 |
|  17 |        VIEW			    |				 |     1 |    78 |     4  (25)| 00:00:01 |
|* 18 | 	FILTER			    |				 |	 |	 |	      | 	 |
|  19 | 	 HASH GROUP BY		    |				 |     1 |    39 |     4  (25)| 00:00:01 |
|* 20 | 	  VIEW			    |				 |     1 |    39 |     4  (25)| 00:00:01 |
|  21 | 	   RESULT CACHE 	    | dyqrs00u554qffvsw6akf32p2p |	 |	 |	      | 	 |
|* 22 | 	    VIEW		    |				 |     1 |   103 |     4  (25)| 00:00:01 |
|  23 | 	     WINDOW SORT	    |				 |     1 |   194 |     4  (25)| 00:00:01 |
|* 24 | 	      TABLE ACCESS FULL     | MLOG$_ORDER_LINES 	 |     1 |   194 |     3   (0)| 00:00:01 |
|* 25 |        INDEX UNIQUE SCAN	    | I_SNAP$_ORDER_SUMMARY_RTMV |   999 | 16983 |     0   (0)| 	 |
|  26 |      NESTED LOOPS		    |				 |     1 |    98 |     6  (34)| 00:00:01 |
|  27 |       VIEW			    |				 |     1 |    81 |     5  (40)| 00:00:01 |
|  28 |        HASH GROUP BY		    |				 |     1 |    39 |     5  (40)| 00:00:01 |
|  29 | 	VIEW			    |				 |     1 |    39 |     4  (25)| 00:00:01 |
|  30 | 	 RESULT CACHE		    | dyqrs00u554qffvsw6akf32p2p |	 |	 |	      | 	 |
|* 31 | 	  VIEW			    |				 |     1 |   103 |     4  (25)| 00:00:01 |
|  32 | 	   WINDOW SORT		    |				 |     1 |   194 |     4  (25)| 00:00:01 |
|* 33 | 	    TABLE ACCESS FULL	    | MLOG$_ORDER_LINES 	 |     1 |   194 |     3   (0)| 00:00:01 |
|* 34 |       MAT_VIEW ACCESS BY INDEX ROWID| ORDER_SUMMARY_RTMV	 |     1 |    17 |     1   (0)| 00:00:01 |
|* 35 |        INDEX UNIQUE SCAN	    | I_SNAP$_ORDER_SUMMARY_RTMV |     1 |	 |     0   (0)| 	 |
------------------------------------------------------------------------------------------------------------------

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

   3 - filter("AV$0"."OJ_MARK" IS NULL)
   4 - access(SYS_OP_MAP_NONNULL("ORDER_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
   5 - filter("ORDER_SUMMARY_RTMV"."ORDER_ID"=1)
  10 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR
	      (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
  12 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-16 19:11:21', 'syyyy-mm-dd hh24:mi:ss'))
  15 - filter(CASE  WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END  IS NULL)
  18 - filter(SUM(1)>0)
  20 - filter("MAS$"."ORDER_ID"=1)
  22 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR
	      (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
  24 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-16 19:11:21', 'syyyy-mm-dd hh24:mi:ss'))
  25 - access("ORDER_SUMMARY_RTMV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
  31 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR
	      (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
  33 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-16 19:11:21', 'syyyy-mm-dd hh24:mi:ss'))
  34 - filter(("ORDER_SUMMARY_RTMV"."ORDER_ID"=1 AND "ORDER_SUMMARY_RTMV"."ROW_COUNT"+"AV$0"."D0">0))
  35 - access("ORDER_SUMMARY_RTMV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

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

   9 -
  21 -
  30 -

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


83 rows selected.

SQL>

We can see the row count is now 96 and the execution plan includes additional work to complete the wind-forward.

Direct Query of Materialized View (FRESH_MV Hint)

In addition to the query rewrites, we also have the ability to query materialized views directly. When we do this we get the current contents of the materialized view by default.

SELECT order_id,
       sum_line_qty,
       sum_total_value,
       row_count
FROM   order_summary_rtmv
WHERE  order_id = 1;

  ORDER_ID SUM_LINE_QTY SUM_TOTAL_VALUE  ROW_COUNT
---------- ------------ --------------- ----------
	 1	    880        44573.88 	95

SQL>


SET LINESIZE 200 PAGESIZE 100
SELECT *
FROM   dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID	8tq6wzmccuzfm, child number 0
-------------------------------------
SELECT order_id,	sum_line_qty,	     sum_total_value,
row_count FROM	 order_summary_rtmv WHERE  order_id = 1

Plan hash value: 3344356712

-------------------------------------------------------------------------------------------
| Id  | Operation	     | Name		  | Rows  | Bytes | Cost (%CPU)| Time	  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |			  |	  |	  |	4 (100)|	  |
|*  1 |  MAT_VIEW ACCESS FULL| ORDER_SUMMARY_RTMV |	1 |    17 |	4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter("ORDER_ID"=1)


19 rows selected.

SQL>

The FRESH_VM hint tells Oracle we want to take advantage of the real-time functionality when doing a direct query against the materialized view, which is why we see a row count of 96 again.

SELECT /*+ FRESH_MV */
       order_id,
       sum_line_qty,
       sum_total_value,
       row_count
FROM   order_summary_rtmv
WHERE  order_id = 1;

  ORDER_ID SUM_LINE_QTY SUM_TOTAL_VALUE  ROW_COUNT
---------- ------------ --------------- ----------
	 1	    910        54573.88 	96

SQL>


SET LINESIZE 200 PAGESIZE 100
SELECT *
FROM   dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID	0pqhrf8c5kbgz, child number 0
-------------------------------------
SELECT /*+ FRESH_MV */	      order_id,        sum_line_qty,
sum_total_value,	row_count FROM	 order_summary_rtmv WHERE
order_id = 1

Plan hash value: 1640379716

------------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name			 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |				 |	 |	 |    20 (100)| 	 |
|   1 |  VIEW				    |				 |  1001 | 52052 |    20  (30)| 00:00:01 |
|   2 |   UNION-ALL			    |				 |	 |	 |	      | 	 |
|*  3 |    FILTER			    |				 |	 |	 |	      | 	 |
|*  4 |     HASH JOIN OUTER		    |				 |     1 |    33 |     9  (23)| 00:00:01 |
|*  5 |      MAT_VIEW ACCESS FULL	    | ORDER_SUMMARY_RTMV	 |     1 |    17 |     4   (0)| 00:00:01 |
|   6 |      VIEW			    |				 |     1 |    16 |     5  (40)| 00:00:01 |
|   7 |       HASH GROUP BY		    |				 |     1 |    39 |     5  (40)| 00:00:01 |
|   8 |        VIEW			    |				 |     1 |    39 |     4  (25)| 00:00:01 |
|   9 | 	RESULT CACHE		    | dyqrs00u554qffvsw6akf32p2p |	 |	 |	      | 	 |
|* 10 | 	 VIEW			    |				 |     1 |   103 |     4  (25)| 00:00:01 |
|  11 | 	  WINDOW SORT		    |				 |     1 |   194 |     4  (25)| 00:00:01 |
|* 12 | 	   TABLE ACCESS FULL	    | MLOG$_ORDER_LINES 	 |     1 |   194 |     3   (0)| 00:00:01 |
|  13 |    VIEW 			    |				 |  1000 | 52000 |    11  (37)| 00:00:01 |
|  14 |     UNION-ALL			    |				 |	 |	 |	      | 	 |
|* 15 |      FILTER			    |				 |	 |	 |	      | 	 |
|  16 |       NESTED LOOPS OUTER	    |				 |   999 | 94905 |     5  (40)| 00:00:01 |
|  17 |        VIEW			    |				 |     1 |    78 |     5  (40)| 00:00:01 |
|* 18 | 	FILTER			    |				 |	 |	 |	      | 	 |
|  19 | 	 HASH GROUP BY		    |				 |     1 |    39 |     5  (40)| 00:00:01 |
|* 20 | 	  VIEW			    |				 |     1 |    39 |     4  (25)| 00:00:01 |
|  21 | 	   RESULT CACHE 	    | dyqrs00u554qffvsw6akf32p2p |	 |	 |	      | 	 |
|* 22 | 	    VIEW		    |				 |     1 |   103 |     4  (25)| 00:00:01 |
|  23 | 	     WINDOW SORT	    |				 |     1 |   194 |     4  (25)| 00:00:01 |
|* 24 | 	      TABLE ACCESS FULL     | MLOG$_ORDER_LINES 	 |     1 |   194 |     3   (0)| 00:00:01 |
|* 25 |        INDEX UNIQUE SCAN	    | I_SNAP$_ORDER_SUMMARY_RTMV |   999 | 16983 |     0   (0)| 	 |
|  26 |      NESTED LOOPS		    |				 |     1 |    98 |     6  (34)| 00:00:01 |
|  27 |       VIEW			    |				 |     1 |    81 |     5  (40)| 00:00:01 |
|  28 |        HASH GROUP BY		    |				 |     1 |    39 |     5  (40)| 00:00:01 |
|  29 | 	VIEW			    |				 |     1 |    39 |     4  (25)| 00:00:01 |
|  30 | 	 RESULT CACHE		    | dyqrs00u554qffvsw6akf32p2p |	 |	 |	      | 	 |
|* 31 | 	  VIEW			    |				 |     1 |   103 |     4  (25)| 00:00:01 |
|  32 | 	   WINDOW SORT		    |				 |     1 |   194 |     4  (25)| 00:00:01 |
|* 33 | 	    TABLE ACCESS FULL	    | MLOG$_ORDER_LINES 	 |     1 |   194 |     3   (0)| 00:00:01 |
|* 34 |       MAT_VIEW ACCESS BY INDEX ROWID| ORDER_SUMMARY_RTMV	 |     1 |    17 |     1   (0)| 00:00:01 |
|* 35 |        INDEX UNIQUE SCAN	    | I_SNAP$_ORDER_SUMMARY_RTMV |     1 |	 |     0   (0)| 	 |
------------------------------------------------------------------------------------------------------------------

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

   3 - filter("AV$0"."OJ_MARK" IS NULL)
   4 - access(SYS_OP_MAP_NONNULL("ORDER_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
   5 - filter("ORDER_SUMMARY_RTMV"."ORDER_ID"=1)
  10 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR
	      (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
  12 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-16 19:11:21', 'syyyy-mm-dd hh24:mi:ss'))
  15 - filter(CASE  WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END  IS NULL)
  18 - filter(SUM(1)>0)
  20 - filter("MAS$"."ORDER_ID"=1)
  22 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR
	      (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
  24 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-16 19:11:21', 'syyyy-mm-dd hh24:mi:ss'))
  25 - access("ORDER_SUMMARY_RTMV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
  31 - filter((("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$") OR
	      (INTERNAL_FUNCTION("MAS$"."OLD_NEW$$") AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")))
  33 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-16 19:11:21', 'syyyy-mm-dd hh24:mi:ss'))
  34 - filter(("ORDER_SUMMARY_RTMV"."ORDER_ID"=1 AND "ORDER_SUMMARY_RTMV"."ROW_COUNT"+"AV$0"."D0">0))
  35 - access("ORDER_SUMMARY_RTMV"."SYS_NC00005$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

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

   9 -
  21 -
  30 -

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


83 rows selected.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.