8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
- Introduction
- Setup
- Materialized View Logs
- Materialized View
- Basic Rewrite
- Rewrite Plus Real-Time Refresh
- Direct Query of Materialized View (FRESH_MV Hint)
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.
The real-time materialized functionality has some restrictions associated with it including the following.
- It is only available if the
QUERY_REWRITE_INTEGRITYparameter is set toENFORCED(the default) orTRUSTED. If theQUERY_REWRITE_INTEGRITYparameter is set toSTALE_TOLERATED, Oracle will not wind forward the data in a stale materialized view. - This can't be used in conjunction with a materialized view using the
REFRESH ... ON COMMIToption. - The materialized view must be capable of a fast refresh, so all the typical fast refresh restrictions apply here also.
- The materialized view can't use database links. I don't think this is a problem as I see this as a solution for real-time reporting and dashboards, rather than part of a distributed environment.
- The materialized view must use the
ENABLE ON QUERY COMPUTATIONoption. - Queries making direct references to a materialized view will not use the real-time materialized view functionality by default. To use this functionality the query much use the
FRESH_MVhint.
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...