Manage a fast refreshable materialized view
High Availability -> Database Advanced Replication -> 3 Materialized View Concepts and Architecture
Database SQL Language Reference
What is a Materialized View?
A materialized view is an object that contains precomputed result of a query against local or remote table, view or materialized views which are also called master tables.
There are three types of materialized views: read only, updatable and writable.
(Note: Create a test user or use sample schema like hr to create and test the materialized view as sys/system schema will not allow to create mviews on its objects. Moreover you will need connect, resource and create materialized view privileges on test schema).
SQL> CREATE MATERIALIZED VIEW mv_dba_objects AS SELECT * FROM dba_objects;
SQL> SELECT owner, mview_name, query, updatable FROM dba_mviews WHERE mview_name='MV_DBA_OBJECTS';
Oracle allows you to create primary key, rowid and complex materialized views
SQL> CREATE MATERIALIZED VIEW mv_dba_objects2 REFRESH WITH PRIMARY KEY AS SELECT * FROM dba_objects;
As the master table changes, the materialized view needs to be refreshed updated. This process is called materialized view refresh.
There are two refresh modes of materialized views available in Oracle: ON COMMIT and ON DEMAND
There are three procedures that are used to refresh a materialized view:
- DBMS_MVIEW.REFRESH is used to refresh a one or more materialized view.
- DBMS_MVIEW.REFRESH_DEPENDENT procedure is used to refresh all materialized views that depend on the same master table.
- DBMS_MVIEW.REFRESH_ALL_MVIEWS procedure is used to refresh all materialized views.
Materialized view logs are used to reduce the cost of the replication process by capturing changes made to the master table and storing them to the different table and is required when the fast refresh mode is used.
When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.
Using the following command, we create a materialized view where the content is purged every 10 days automatically:
SQL> CREATE MATERIALIZED VIEW LOG ON mytable TABLESPACE users PURGE REPEAT INTERVAL ’10’ DAY;
When refreshing a materialized view, you have the following four refresh options:
1. FAST option uses materialized view log which tracks the changes on the master table to pull changes made to the master table after last refresh. If the materialized view log is not available, the command will fail.
2. COMPLETE option truncates the materialized view object and repopulate it from the master table.
3. FORCE option tries FAST refresh, and then COMPLETE refresh if the first try fails.
4. NEVER option doesn’t allow the materialized view to be refreshed.
SQL> CREATE TABLE org_table AS SELECT object_id, object_name, object_type FROM dba_objects WHERE ROWNUM <= 100;
SQL> CREATE MATERIALIZED VIEW mview01 BUILD IMMEDIATE REFRESH COMPLETE AS SELECT * FROM org_table;
SQL> SELECT COUNT(1) FROM mview01;
COUNT(1)
----------
100
SQL> CREATE MATERIALIZED VIEW mview02 BUILD DEFERRED REFRESH FORCE ON DEMAND AS SELECT * FROM org_table;
SQL> SELECT COUNT(1) FROM mview02;
COUNT(1)
----------
0
SQL> EXEC DBMS_MVIEW.REFRESH('mview02');
SQL> SELECT COUNT(1) FROM mview02;
COUNT(1)
----------
100
Now let’s create a materialized view and make the refresh happen automatically each time when the master table changes
SQL> ALTER TABLE org_table ADD PRIMARY KEY (object_id);
SQL> CREATE MATERIALIZED VIEW LOG ON org_table TABLESPACE users PURGE REPEAT INTERVAL '10' DAY;
SQL> CREATE MATERIALIZED VIEW mview03 BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM org_table;
(Note you should be given "grant create MATERIALIZED VIEW to hr;" before creating mview)
SQL> SELECT COUNT(1) FROM mview03;
COUNT(1)
----------
100
SQL> SELECT COUNT(1) FROM org_table;
COUNT(1)
----------
100
SQL> INSERT INTO org_table VALUES(200,'MyOBJECT','TABLE');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(1) FROM org_table;
COUNT(1)
----------
101
SQL> SELECT COUNT(1) FROM mview03;
COUNT(1)
----------
101
Next, create a materialized view and schedule refresh process for every minute. You don’t need to create a separate job to automate the refresh process. By using START WITH and NEXT clauses you specify the first and next refreshes of materialized view.
SQL> CREATE MATERIALIZED VIEW mview04 REFRESH COMPLETE START WITH (SYSDATE) NEXT SYSDATE + INTERVAL '1' MINUTE AS SELECT * FROM org_table;
Now if you query DBA_JOBS view, you will see that the refresh job is automatically created. Also if you try to insert another row in org_table, it will refresh after 1 min in the mview04.
To stop the auto-refresh process, it is enough to disable the job using DBMS_JOB.BROKEN procedure as follows:
SQL> exec DBMS_JOB.BROKEN (44, TRUE );
Manage a fast refreshable materialized view – query rewrite
Data Warehousing and Business Intelligence -> Database Data Warehousing Guide -> 18 Basic Query Rewrite
Master Note for Query Rewrite (Doc ID 1215173.1)
SQL> CREATE TABLE hr.mview_objects as SELECT * FROM dba_objects WHERE object_id IS NOT NULL;
Table created.
SQL> CREATE TABLE mview_extents as SELECT * FROM dba_extents;
Table created.
SQL> explain plan for SELECT a.OBJECT_NAME, COUNT(EXTENT_ID) FROM MVIEW_OBJECTS a, MVIEW_EXTENTS b WHERE a.OBJECT_NAME = b.SEGMENT_NAME GROUP BY a.OBJECT_NAME;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1791746118
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3825 | 179K| 454 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 3825 | 179K| 454 (1)| 00:00:01 |
|* 2 | HASH JOIN | | 12945 | 606K| 453 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| MVIEW_EXTENTS | 7562 | 169K| 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| MVIEW_OBJECTS | 91736 | 2239K| 429 (1)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
SQL> CREATE MATERIALIZED VIEW LOG ON mview_objects WITH ROWID, SEQUENCE(object_name) INCLUDING NEW VALUES;
SQL> CREATE MATERIALIZED VIEW LOG ON mview_extents WITH ROWID, SEQUENCE(extent_id, segment_name) INCLUDING NEW VALUES;
SQL> CREATE MATERIALIZED VIEW mview_seg_ext REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT a.object_name, COUNT (extent_id) FROM mview_objects a, mview_extents b WHERE a.object_name = b.segment_name GROUP BY a.object_name;
Materialized view created.
SQL> explain plan for SELECT a.OBJECT_NAME, COUNT(EXTENT_ID) FROM MVIEW_OBJECTS a, MVIEW_EXTENTS b WHERE a.OBJECT_NAME = b.SEGMENT_NAME GROUP BY a.OBJECT_NAME;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 4076247382
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3824 | 91776 | 7 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_SEG_EXT | 3824 | 91776 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=FALSE;
Session altered.
SQL> explain plan for SELECT a.OBJECT_NAME, COUNT(EXTENT_ID) FROM MVIEW_OBJECTS a, MVIEW_EXTENTS b WHERE a.OBJECT_NAME = b.SEGMENT_NAME GROUP BY a.OBJECT_NAME;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1791746118
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3825 | 179K| 454 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 3825 | 179K| 454 (1)| 00:00:01 |
|* 2 | HASH JOIN | | 12945 | 606K| 453 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| MVIEW_EXTENTS | 7562 | 169K| 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| MVIEW_OBJECTS | 91736 | 2239K| 429 (1)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
Moreover, you can use DBMS_MVIEW.EXPLAIN_REWRITE procedure to learn why query rewrite failed. To get more information about this procedure, check the following documentations:
Database PL/SQL Packages and Types Reference -> 93 DBMS_MVIEW -> Data Warehousing and Business Intelligence -> Database Data Warehousing Guide -> 18 Advanced Query Rewrite -> Verifying that Query Rewrite has Occurred
Execute utlxrw.sql file to create a table REWRITE_TABLE where the output of EXPLAIN_REWRITE procedure will be forwarded as follows:
[oracle@dbgenre admin]$ sqlplus system/oracle
SQL> @/app/oracle/product/12.1.0/dbhome1/rdbms/admin/utlxrw.sql
SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED=FALSE;
SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE ('SELECT a.OBJECT_NAME, COUNT(EXTENT_ID) FROM MVIEW_OBJECTS a, MVIEW_EXTENTS b WHERE a.OBJECT_NAME = b.SEGMENT_NAME GROUP BY a.OBJECT_NAME', 'mview_seg_ext', 'HR');
PL/SQL procedure successfully completed.
SQL> SELECT message FROM rewrite_table ORDER BY sequence;
MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01001: query rewrite not enabled
Hope this article was useful.
Regards,
Balaaji Dhananjayan