Understanding RealTime Materialized View In Oracle Database 19c

pkg_caching

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)

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *