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>
Really great blog, it’s very helpful and has great knowledgeable information. Thanks for sharing, keep updating such a good informative blog.
Best Software company in New jersey, USA
Great article, really very helpful content you made. Thank you, keep sharing. Quality Managment Services | Austere Technologies
Very good informative article. Thanks for sharing such nice article, keep on updating such good articles. Internet Of Things(?IOT) Services | Austere Technologies