Loading....

One of the new features in Oracle 12c Release 2 is real-time refreshable Materialized Views.
 
In earlier releases, even if a single row in the base table got changed, the underlying materialized view was marked as stale and this prevented any query rewrite operations until the materialized view was refreshed again.
 
The ON COMMIT refresh mode did provide real-time data by ensuring that the materialized view is updated along with any changes made to the base tables.
 
However, when the volume of DML changes to the base tables was very high as well as very frequent, this mode often resulted in resource contention and reduced refresh performance. Further, the ON COMMIT refresh mode was quite restrictive in its ability to support query rewrite operations.
 
A real-time materialized view provides fresh data to user queries even when the materialized view is not in sync with its base tables because of data change in the base table. Real-time materialized views obtain fresh data from stale materialized views by recomputing the data on the fly.
 
Whenever a query accesses a real-time materialized view, the database will first check to see whether the real-time materialized view is marked as stale or not. If it is not marked as stale, then the data required to satisfy the query will be provided using the real-time materialized view directly.
 
If the real-time materialized view is marked as stale, then the on-query computation technique is used to recompute and generate the fresh data on the fly and return the correct query result.
 
A real-time materialized view is created by using the ON QUERY COMPUTATION clause in the CREATE MATERIALIZED VIEW command.

 

Create and populate the MYSALES table
 

SQL> create table mysales
 (prod_id number not null,
   amount_sold number not null,
  quantity_sold number not null); 

Table created.


SQL> insert /*+ append */ into mysales
  select mod(rownum,1000),
  trunc(dbms_random.value(1,10)),
  mod(rownum,20)
  from dual connect by level <= 100000; 

100000 rows created.

 

Create the MYSALES_MV materialized view

 

SQL> CREATE MATERIALIZED VIEW LOG ON MYSALES WITH ROWID
    (prod_id,amount_sold,quantity_sold)
    INCLUDING NEW VALUES;  

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MYSALES_MV
 REFRESH FAST ON DEMAND
 ENABLE QUERY REWRITE
   AS
   SELECT prod_id, SUM(amount_sold) sas, SUM(quantity_sold)  sqs
  FROM mysales
  GROUP BY prod_id;  

Materialized view created.

Verify Query Rewrite
 

SQL> set autotrace traceonly explain

SQL> select prod_id, SUM(amount_sold), SUM(quantity_sold) 
  FROM mysales
 GROUP BY prod_id;  

Execution Plan
----------------------------------------------------------
Plan hash value: 1046649047

--------------------------------------------------------------------------------
-----------

| Id  | Operation		     | Name	  | Rows  | Bytes | Cost (%CPU)|
 Time	  |

--------------------------------------------------------------------------------
-----------

|   0 | SELECT STATEMENT	     |		  |  1000 | 39000 |	3   (0)|
 00:00:01 |

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MYSALES_MV |  1000 | 39000 |	3   (0)|
 00:00:01 |

--------------------------------------------------------------------------------
-----------

 
We will now insert a row into the base table and note that the materialized view is now become stale. Query rewrite does not happen any more and a full table scan is being performed of the base table instead.
 

SQL> insert into mysales
 values
   (1,1,10);  

1 row created.

SQL> commit;

Commit complete.

SQL> COLUMN mview_name FORMAT A30

SELECT mview_name,
       staleness,
       on_query_computation
FROM   user_mviews;

MVIEW_NAME		       STALENESS	   O
------------------------------ ------------------- -
MYSALES_MV		       NEEDS_COMPILE	   Y



SQL> set autotrace traceonly explain

SQL> set autotrace traceonly explain
SQL> select prod_id, SUM(amount_sold), SUM(quantity_sold) 
 FROM mysales
  GROUP BY prod_id;  

Execution Plan
----------------------------------------------------------
Plan hash value: 4236506231

------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     |	 111K|	4228K|	  63   (7)| 00:00:01 |
|   1 |  HASH GROUP BY	   |	     |	 111K|	4228K|	  63   (7)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MYSALES |	 111K|	4228K|	  60   (2)| 00:00:01 |
------------------------------------------------------------------------------

Convert the materialized view to enable on query computation
 

SQL> alter materialized view mysales_mv enable on query computation; 

Materialized view altered.

 

We now insert another row into the base table and even though the materialized view has stale data, fresh data is being provided for the query by recomputing and obtaining the data by querying the base table.
 

SQL>  insert into mysales
 values
   (1,1,10);  

1 row created.

SQL> commit;

Commit complete.

SQL>  set autotrace traceonly explain

SQL> select prod_id, SUM(amount_sold), SUM(quantity_sold) 
 FROM mysales
  GROUP BY prod_id;  

Execution Plan
----------------------------------------------------------
Plan hash value: 2000063866

--------------------------------------------------------------------------------
------------------------

| Id  | Operation			  | Name	       | Rows  | Bytes |
 Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT		  |		       |  2001 | 78039 |
    16	(19)| 00:00:01 |

|   1 |  VIEW				  |		       |  2001 | 78039 |
    16	(19)| 00:00:01 |

|   2 |   UNION-ALL			  |		       |       |       |
	    |	       |

|*  3 |    VIEW 			  | VW_FOJ_0	       |  1000 | 42000 |
     6	(17)| 00:00:01 |

|*  4 |     HASH JOIN FULL OUTER	  |		       |  1000 | 54000 |
     6	(17)| 00:00:01 |

|   5 |      VIEW			  |		       |     1 |    24 |
     3	(34)| 00:00:01 |

|   6 |       HASH GROUP BY		  |		       |     1 |    48 |
     3	(34)| 00:00:01 |

|*  7 |        TABLE ACCESS FULL	  | MLOG$_MYSALES      |     1 |    48 |
     2	 (0)| 00:00:01 |

|   8 |      VIEW			  |		       |  1000 | 30000 |
     3	 (0)| 00:00:01 |

|   9 |       MAT_VIEW ACCESS FULL	  | MYSALES_MV	       |  1000 | 39000 |
     3	 (0)| 00:00:01 |

|* 10 |    VIEW 			  | VW_FOJ_1	       |  1000 | 68000 |
     6	(17)| 00:00:01 |

|* 11 |     HASH JOIN FULL OUTER	  |		       |  1000 | 53000 |
     6	(17)| 00:00:01 |

|  12 |      VIEW			  |		       |     1 |    45 |
     3	(34)| 00:00:01 |

|  13 |       HASH GROUP BY		  |		       |     1 |    48 |
     3	(34)| 00:00:01 |

|* 14 |        TABLE ACCESS FULL	  | MLOG$_MYSALES      |     1 |    48 |
     2	 (0)| 00:00:01 |

|  15 |      VIEW			  |		       |  1000 |  8000 |
     3	 (0)| 00:00:01 |

|  16 |       MAT_VIEW ACCESS FULL	  | MYSALES_MV	       |  1000 | 39000 |
     3	 (0)| 00:00:01 |

|  17 |    NESTED LOOPS 		  |		       |     1 |   120 |
     4	(25)| 00:00:01 |

|  18 |     VIEW			  |		       |     1 |    68 |
     3	(34)| 00:00:01 |

|  19 |      HASH GROUP BY		  |		       |     1 |    48 |
     3	(34)| 00:00:01 |

|* 20 |       TABLE ACCESS FULL 	  | MLOG$_MYSALES      |     1 |    48 |
     2	 (0)| 00:00:01 |

|  21 |     MAT_VIEW ACCESS BY INDEX ROWID| MYSALES_MV	       |     1 |    52 |
     1	 (0)| 00:00:01 |

|* 22 |      INDEX UNIQUE SCAN		  | I_SNAP$_MYSALES_MV |     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("SNA$0"."PROD_ID")=SYS_OP_MAP_NONNULL("AV$0"."G
B0"))

   7 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-12-19 20:09:13', 'syyyy-mm-dd h
h24:mi:ss'))

  10 - filter("SNA$0"."SNA_OJ_MARK" IS NULL)
  11 - access(SYS_OP_MAP_NONNULL("SNA$0"."PROD_ID")=SYS_OP_MAP_NONNULL("AV$0"."G
B0"))

  14 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-12-19 20:09:13', 'syyyy-mm-dd h
h24:mi:ss'))

  20 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-12-19 20:09:13', 'syyyy-mm-dd h
h24:mi:ss'))

  22 - access(SYS_OP_MAP_NONNULL("PROD_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

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

 
Verify that the query was rewritten using the on query computation
 

SQL> @?/rdbms/admin/utlxrw.sql

SQL> begin
 DBMS_MVIEW.EXPLAIN_REWRITE('SELECT prod_id, SUM(quantity_sold), SUM(amount_sold) FROM mysales GROUP BY prod_id','MYSALES_MV','x');
  end;
 /  

PL/SQL procedure successfully completed.


SQL> select message from rewrite_table;

MESSAGE
--------------------------------------------------------------------------------
QSM-01151: query was rewritten

QSM-01209: query rewritten with materialized view, MYSALES_MV, using text match algorithm

QSM-01357: query rewritten with a stale materialized view, MYSALES_MV, by on query computation

QSM-01029: materialized view, MYSALES_MV, is stale in ENFORCED integrity mode

 
Query the materialized view directly using the FRESH_MV hint
 
We insert a new row into the base table and can obtain the fresh data from the materialized view using the FRESH_MV hint (without having to refresh the MV)
 


SQL>  SELECT 
   prod_id, sas, sqs
   FROM mysales_mv
   where prod_id=1;

   PROD_ID	  SAS	     SQS
---------- ---------- ----------
	 1	  505	     130

SQL> insert into mysales
 values
   (1,1,10);   

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT /*+ FRESH_MV */
   prod_id, sas, sqs
   FROM mysales_mv
   where prod_id=1;

   PROD_ID	  SAS	     SQS
---------- ---------- ----------
	 1	  506	     140

SQL> 
Please follow and like us:

Last Update: June 22, 2020  

December 26, 2017 16 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

3 thoughts on “Oracle 12c Release 2 New Feature Real-Time Refreshable Materialized Views

Leave a Comment

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

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?