In earlier Oracle version, QUERY REWRITE feature used to stop working as soon as the materialized view become STALE, unless the QUERY_REWRITE_INTEGRITY parameter is set to “STALE_TOLERATED”, there was no way to take the advantage of QUERY REWRITE feature.
Oracle 12.2 introduced the concept of real-time materialized view which finally fixed the problem of materialized views with STALE data.
The prerequisites for Real Time MV :::
QUERY_REWRITE_INTEGRITY should be either ENFORCED/TRUSTED.
The materialized view must be REFRESH FAST ON DEMAND.
The materialized view must use the ENABLE ON QUERY COMPUTATION clause.
The idea here is Oracle will dynamically pick the updated values from mview log and will display the result on the fly.
SQL> SHOW PARAMETER QUERY_REWRITE_INTEGRITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_integrity string enforced
SQL> SHOW PARAMETER QUERY_REWRITE_ENABLED
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
SQL> CREATE TABLE emp
2 (
3 empno NUMBER(10),
4 ename VARCHAR2(4000 BYTE),
5 job VARCHAR2(4000 BYTE),
6 mgr NUMBER(10),
7 hiredate DATE,
8 sal NUMBER(10),
9 comm NUMBER(10),
10 deptno NUMBER(10),
11 CONSTRAINT pk_emp PRIMARY KEY (empno)
12 );
Table created.
SQL> INSERT /*+ APPEND */ INTO emp
2 SELECT level AS id,
3 DBMS_RANDOM.STRING('U',5),
4 DBMS_RANDOM.STRING('U',5),
5 TRUNC(DBMS_RANDOM.value(1,1000)) AS mgr,
6 TRUNC(SYSDATE - DBMS_RANDOM.value(0,366)) AS hiredate,
7 TRUNC(DBMS_RANDOM.value(1,20)) AS sal,
8 ROUND(DBMS_RANDOM.value(1,1000),2) AS comm,
9 TRUNC(DBMS_RANDOM.value(1,1000)) AS deptno
10 FROM dual CONNECT BY level <= 100000;
100000 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
100000
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'EMP');
PL/SQL procedure successfully completed.
SQL> CREATE MATERIALIZED VIEW LOG ON emp WITH ROWID,SEQUENCE(sal,comm,deptno) INCLUDING NEW VALUES;
Materialized view log created.
SQL> SELECT * FROM MLOG$_EMP;
no rows selected
SQL> CREATE MATERIALIZED VIEW emp_mview
2 REFRESH FAST ON DEMAND
3 ENABLE QUERY REWRITE
4 ENABLE ON QUERY COMPUTATION
5 AS
6 SELECT deptno,SUM(sal) sum_sal,SUM(comm) sum_comm,COUNT(*) cnt
7 FROM emp
8 GROUP BY deptno;
Materialized view created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER,'EMP_MVIEW');
PL/SQL procedure successfully completed.
SQL> SELECT mview_name,
2 staleness,
3 on_query_computation
4 FROM user_mviews
5 WHERE mview_name = 'EMP_MVIEW';
MVIEW_NAME STALENESS ON_QUERY_COMPUTATION
----------- ------------------- ----------------------
EMP_MVIEW FRESH Y
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT deptno,SUM(sal) sum_sal,SUM(comm) sum_comm,COUNT(*) cnt
2 FROM emp
3 WHERE deptno = 10
4 GROUP BY deptno;
DEPTNO SUM_SAL SUM_COMM CNT
---------- ---------- ---------- ----------
10 1126 58927 116
Execution Plan
----------------------------------------------------------
Plan hash value: 3893662896
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| EMP_MVIEW | 1 | 17 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP_MVIEW"."DEPTNO"=10)
SQL> INSERT INTO emp VALUES(100001,'TESTNAME1','TESTJOB1',NULL,SYSDATE,1000,100,10);
1 row created.
SQL> COMMIT;
Commit complete.
--MVIEW Log Gets Populated Accordingly
SQL> SELECT COUNT(*) FROM MLOG$_EMP;
COUNT(*)
----------
1
SQL> SELECT mview_name,
2 staleness,
3 on_query_computation
4 FROM user_mviews
5 WHERE mview_name = 'EMP_MVIEW';
MVIEW_NAME STALENESS ON_QUERY_COMPUTATION
----------- ------------------- ----------------------
EMP_MVIEW NEEDS_COMPILE Y
--Since we have the ENABLE ON QUERY COMPUTATION option on the materialized view it is still considered usable, as Oracle will dynamically pick the values from materialized view logs.
SQL> SELECT deptno,SUM(sal) sum_sal,SUM(comm) sum_comm,COUNT(*) cnt
2 FROM emp
3 WHERE deptno = 10
4 GROUP BY deptno;
DEPTNO SUM_SAL SUM_COMM CNT
---------- ---------- ---------- ----------
10 2126 59027 117
Execution Plan
----------------------------------------------------------
Plan hash value: 1276233973
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 156 | 18 (28)| 00:00:01 |
| 1 | VIEW | | 3 | 156 | 18 (28)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN OUTER | | 1 | 37 | 8 (25)| 00:00:01 |
|* 5 | MAT_VIEW ACCESS FULL | EMP_MVIEW | 1 | 21 | 3 (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 | 1b6h8h49d4sxj812ydz80zsf01 | 1 | 103 | 4 (25)| 00:00:01 |
|* 10 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |
| 11 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | MLOG$_EMP | 1 | 194 | 3 (0)| 00:00:01 |
| 13 | VIEW | | 2 | 104 | 10 (30)| 00:00:01 |
| 14 | UNION-ALL | | | | | |
|* 15 | FILTER | | | | | |
| 16 | NESTED LOOPS OUTER | | 1 | 95 | 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 | 1b6h8h49d4sxj812ydz80zsf01 | 1 | 103 | 4 (25)| 00:00:01 |
|* 22 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |
| 23 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | MLOG$_EMP | 1 | 194 | 3 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_SNAP$_EMP_MVIEW | 1 | 17 | 0 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 102 | 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 | 1b6h8h49d4sxj812ydz80zsf01 | 1 | 103 | 4 (25)| 00:00:01 |
|* 31 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |
| 32 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |
|* 33 | TABLE ACCESS FULL | MLOG$_EMP | 1 | 194 | 3 (0)| 00:00:01 |
|* 34 | MAT_VIEW ACCESS BY INDEX ROWID| EMP_MVIEW | 1 | 21 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | I_SNAP$_EMP_MVIEW | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AV$0"."OJ_MARK" IS NULL)
4 - access(SYS_OP_MAP_NONNULL("DEPTNO")=SYS_OP_MAP_NONNULL("AV$0"."GB0"(+)))
5 - filter("EMP_MVIEW"."DEPTNO"=10)
10 - filter("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR ("MAS$"."OLD_NEW$$"='O' OR
"MAS$"."OLD_NEW$$"='U') AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
12 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2022-05-17 22:07:58', '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$"."DEPTNO"=10)
22 - filter("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR ("MAS$"."OLD_NEW$$"='O' OR
"MAS$"."OLD_NEW$$"='U') AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
24 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2022-05-17 22:07:58', 'syyyy-mm-dd hh24:mi:ss'))
25 - access("EMP_MVIEW"."SYS_NC00005$"(+)=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
31 - filter("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR ("MAS$"."OLD_NEW$$"='O' OR
"MAS$"."OLD_NEW$$"='U') AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
33 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2022-05-17 22:07:58', 'syyyy-mm-dd hh24:mi:ss'))
34 - filter("EMP_MVIEW"."DEPTNO"=10 AND "EMP_MVIEW"."CNT"+"AV$0"."D0">0)
35 - access(SYS_OP_MAP_NONNULL("DEPTNO")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
Result Cache Information (identified by operation id):
------------------------------------------------------
9 - column-count=8; dependencies=(COURSE.MLOG$_EMP); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_EMP"
21 - column-count=8; dependencies=(COURSE.MLOG$_EMP); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_EMP"
30 - column-count=8; dependencies=(COURSE.MLOG$_EMP); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_EMP"
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> SELECT mview_name,
2 staleness,
3 on_query_computation
4 FROM user_mviews
5 WHERE mview_name = 'EMP_MVIEW';
MVIEW_NAME STALENESS ON_QUERY_COMPUTATION
----------- ------------------- ----------------------
EMP_MVIEW STALE Y
--Direct Reference To MVIEW Will Take Advantage of QUERY REWRITE.
SQL> SELECT deptno,sum_sal,sum_comm,cnt
2 FROM emp_mview
3 WHERE deptno = 10;
DEPTNO SUM_SAL SUM_COMM CNT
---------- ---------- ---------- ----------
10 1126 58927 116
Execution Plan
----------------------------------------------------------
Plan hash value: 470571830
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | MAT_VIEW ACCESS FULL| EMP_MVIEW | 1 | 17 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
SQL> SELECT /*+ FRESH_MV */ deptno,sum_sal,sum_comm,cnt
2 FROM emp_mview
3 WHERE deptno = 10;
DEPTNO SUM_SAL SUM_COMM CNT
---------- ---------- ---------- ----------
10 2126 59027 117
Execution Plan
----------------------------------------------------------
Plan hash value: 1276233973
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 156 | 18 (28)| 00:00:01 |
| 1 | VIEW | | 3 | 156 | 18 (28)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN OUTER | | 1 | 37 | 8 (25)| 00:00:01 |
|* 5 | MAT_VIEW ACCESS FULL | EMP_MVIEW | 1 | 21 | 3 (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 | 1b6h8h49d4sxj812ydz80zsf01 | 1 | 103 | 4 (25)| 00:00:01 |
|* 10 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |
| 11 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | MLOG$_EMP | 1 | 194 | 3 (0)| 00:00:01 |
| 13 | VIEW | | 2 | 104 | 10 (30)| 00:00:01 |
| 14 | UNION-ALL | | | | | |
|* 15 | FILTER | | | | | |
| 16 | NESTED LOOPS OUTER | | 1 | 95 | 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 | 1b6h8h49d4sxj812ydz80zsf01 | 1 | 103 | 4 (25)| 00:00:01 |
|* 22 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |
| 23 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | MLOG$_EMP | 1 | 194 | 3 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_SNAP$_EMP_MVIEW | 1 | 17 | 0 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 102 | 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 | 1b6h8h49d4sxj812ydz80zsf01 | 1 | 103 | 4 (25)| 00:00:01 |
|* 31 | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |
| 32 | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |
|* 33 | TABLE ACCESS FULL | MLOG$_EMP | 1 | 194 | 3 (0)| 00:00:01 |
|* 34 | MAT_VIEW ACCESS BY INDEX ROWID| EMP_MVIEW | 1 | 21 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | I_SNAP$_EMP_MVIEW | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AV$0"."OJ_MARK" IS NULL)
4 - access(SYS_OP_MAP_NONNULL("DEPTNO")=SYS_OP_MAP_NONNULL("AV$0"."GB0"(+)))
5 - filter("EMP_MVIEW"."DEPTNO"=10)
10 - filter("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR ("MAS$"."OLD_NEW$$"='O' OR
"MAS$"."OLD_NEW$$"='U') AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
12 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2022-05-17 22:07:58', '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$"."DEPTNO"=10)
22 - filter("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR ("MAS$"."OLD_NEW$$"='O' OR
"MAS$"."OLD_NEW$$"='U') AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
24 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2022-05-17 22:07:58', 'syyyy-mm-dd hh24:mi:ss'))
25 - access("EMP_MVIEW"."SYS_NC00005$"(+)=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
31 - filter("MAS$"."OLD_NEW$$"='N' AND "MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR ("MAS$"."OLD_NEW$$"='O' OR
"MAS$"."OLD_NEW$$"='U') AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
33 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2022-05-17 22:07:58', 'syyyy-mm-dd hh24:mi:ss'))
34 - filter("EMP_MVIEW"."DEPTNO"=10 AND "EMP_MVIEW"."CNT"+"AV$0"."D0">0)
35 - access(SYS_OP_MAP_NONNULL("DEPTNO")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
Result Cache Information (identified by operation id):
------------------------------------------------------
9 - column-count=8; dependencies=(COURSE.MLOG$_EMP); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_EMP"
21 - column-count=8; dependencies=(COURSE.MLOG$_EMP); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_EMP"
30 - column-count=8; dependencies=(COURSE.MLOG$_EMP); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_EMP"
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--Fast Refresh
SQL> EXEC DBMS_MVIEW.REFRESH('EMP_MVIEW');
PL/SQL procedure successfully completed.
MVIEW_NAME STALENESS ON_QUERY_COMPUTATION
----------- ------------------- ----------------------
EMP_MVIEW FRESH Y
SQL> SELECT deptno,SUM(sal) sum_sal,SUM(comm) sum_comm,COUNT(*) cnt
2 FROM emp
3 WHERE deptno = 10
4 GROUP BY deptno;
DEPTNO SUM_SAL SUM_COMM CNT
---------- ---------- ---------- ----------
10 2126 59027 117
Execution Plan
----------------------------------------------------------
Plan hash value: 3893662896
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| EMP_MVIEW | 1 | 17 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP_MVIEW"."DEPTNO"=10)
