In this example, I am creating a materialized view to support queries against a table with 20 million rows, which are looking at the maximum sequence number within a range of dates.
Oracle describes the benefits of Materialized views like this:
"One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables."
Since I want the materialized view to be refreshed every time a new row is commited to the base table, I need to specify that the mview should use "fast refresh".
First, create a materialized view log to store changed rows in the base tables that your materialized views are built on. This is required when using FAST REFRESH:
CREATE MATERIALIZED VIEW LOG ON DATE_TO_SEQNO TABLESPACE DATA1 CACHE ENABLE ROW MOVEMENT WITH ROWID (SEQNO, RDATE) INCLUDING NEW VALUES;
Then, create the materialized view. Query rewrites are disabled by default, so it must be explicitly stated:
CREATE MATERIALIZED VIEW SEQ_AGGR_INFO SEGMENT CREATION IMMEDIATE TABLESPACE DATA1 CACHE ENABLE ROW MOVEMENT ROW STORE COMPRESS ADVANCED BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS select RDATE, count(SEQNO) as antall, max(SEQNO) as makssekvens from DATE_TO_SEQNO group by dato order by 1, 2;
Verify the mview's existence:
SELECT mview_name,rewrite_enabled,refresh_mode,refresh_method, last_refresh_type, last_refresh_date,last_refresh_end_time,staleness,compile_state FROM USER_MVIEWS;
| MVIEW_NAME | REWRITE_ENABLED | REFRESH_MODE | REFRESH_METHOD | LAST_REFRESH_TYPE | LAST_REFRESH_DATE | LAST_REFRESH_END_TIME | STALENESS | COMPILE_STATE |
|---|---|---|---|---|---|---|---|---|
| SEQ_AGGR_INFO | Y | COMMIT | FAST | COMPLETE | 18.01.2019 13:58:24 | 18.01.2019 13:58:24 | FRESH | VALID |
Verify the mview log's existence:
SELECT master,log_table,rowids, primary_key,filter_columns, include_new_values FROM USER_MVIEW_LOGS;
| MASTER | LOG_TABLE | ROWIDS | PRIMARY_KEY | FILTER_COLUMNS | INCLUDE_NEW_VALUES |
|---|---|---|---|---|---|
| DATE_TO_SEQNO | MLOG$_DATE_TO_SEQNO | YES | NO | YES | YES |
The following query will now use the materialized view, due to the QUERY REWRITE directive used in the creation of the mview:
var B1 VARCHAR2(10);
EXEC :B1 := to_date('01.01.2018','dd.mm.yyyy');
set lines 200
set autot on
set timing on
select max(seqno)
from DATE_TO_SEQNO
where rdate = :B1;
The explain plan verifies it:
MAX(SEKVENSNUMMER)
------------------
18580449
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1856567295
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 243 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| SEQ_AGGR_INFO | 1 | 14 | 243 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SEQ_AGGR_INFO"."RDATE"=:B1)
To drop the materialized view and its log object:
drop materialized view log on DATE_TO_SEQNO; drop materialized view SEQ_AGGR_INFO;
Sources:
Create Materialized View Log
Create Materialized view
Datawarehousing guide
