Oracle Materialized Views in 12c

Definition	A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases. Materialized views, which store data based on remote tables are also known as snapshots. When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects. For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.
Materialized View Syntaxes	Alter Materialized View Enable Query Rewrite Including New Values Alter Materialized View Log Fast Refresh On Commit On Prebuilt Table Complete Refresh Fast Refresh On Demand Using Trusted Constraints Complete Refresh Using Index Force Refresh With Sequence Create Refresh Log
Data Dictionary Objects	ALL_REFRESH_DEPENDENCIES GV$MREFRESH SNAP$ COL$ SLOG$ TAB$ DBA_REGISTERED_SNAPSHOT_GROUPS     -- plus the objects returned by the following query SELECT mview_name FROM dba_mviews WHERE mview_name LIKE '%MVIEW%' ORDER BY 1;
Related Packages and Libraries	DBMS_OFFLINE_SNAPSHOT DBMS_SNAPSHOT_LIB DBMS_SNAPSHOT_UTIL DBMS_SNAPSHOT
System Privileges	CREATE ANY MATERIALIZED VIEW DROP ANY MATERIALIZED VIEW SELECT ANY TABLE CREATE ANY TABLE INSERT ANY TABLE UNDER ANY TABLE CREATE MATERIALIZED VIEW LOCK ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE
Related Definitions
Materialized View Log		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 re-execute 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. A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table. To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.
Build		Specifies when to populate the materialized view. Specify IMMEDIATE to indicate that the materialized view is to be populated immediately: The default. Specify DEFERRED to indicate that the materialized view is to be populated by the next REFRESH operation. The first (deferred) refresh must always be a complete refresh. Until then, the materialized view has a staleness value of UNUSABLE, so it cannot be used for query rewrite. Syntax: BUILD <IMMEDIATE | DEFERRED> WITH REDUCED PRECISION Authorizes the loss of precision that will result if the precision of the table or materialized view columns do not exactly match the precision returned by subquery. WITHOUT REDUCED PRECISION Requires that the precision of the table or materialized view columns match exactly the precision returned by the subquery, or the create operation will fail. This is the default
Caching		For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list. Syntax: <CACHE | NOCACHE>
Cluster		Creates materialized views as part of a cluster. A cluster materialized view uses the space allocation of the cluster. Partitioning is not allowed when an MV is built on a cluster.
Complex Materialized View		Each row in the materialized view can not be mapped back to a single row in a source table. For example a materialized view based on an aggregation, concatenation, group by, having, rollup, cube, or similar functionality.
Compression		Use the table_compression clause to instruct the database whether to compress data segments to reduce disk and memory use. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. Syntax: <COMPRESS | NOCOMPRESS>
Organization Index		Create an index-organized materialized view. IOT can be specified for the following: Read-only and updatable object materialized views. Read-only and updatable primary key materialized views. Read-only rowid materialized views. The keywords and parameters of the index_org_table_clause have the same semantics as described in CREATE TABLE, with the restrictions that follow.
Parallel		The parallel_clause indicates whether parallel operations will be supported for the materialized view and sets the default degree of parallelism for queries and DML on the materialized view after creation. Syntax: PARALLEL (DEGREE <INTEGER>)
Partitioning		Materialized views can be partitioned just like any other heap table.
Prebuilt Tables		The ON PREBUILT TABLE clause lets you register an existing table as a preinitialized materialized view. This clause is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view. If the materialized view is dropped, then the preexisting table reverts to its identity as a table.
Query Rewrite		If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside the schema. If you are defining the materialized view on a pre-built container (ON PRE-BUILT TABLE clause), then you must have the SELECT privilege WITH GRANT OPTION on the container table.
Refresh Group		A grouping of materialized views so that they can be refreshed as a single transaction for consistency.
Refresh Log		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 re-execute 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.
Refresh Types		COMPLETE REFRESH FAST REFRESH
Simple Materialized View		Each row in the materialized view can be mapped back to a single row in a source table.
Snapshot		The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility. In 11g and 12c it can be ignored.
Using Index		The USING INDEX clause allow establishing values for INITRANS and STORAGE parameters for the default index used to maintain the materialized view. If USING INDEX is not specified, then default tablespace values are used. The default index is used to speed up incremental (FAST) refresh of the materialized view. Specify USING NO INDEX to suppress the creation of the default index. You can create an alternative index explicitly by using the CREATE INDEX statement. You should create such an index if you specify USING NO INDEX and you are creating the materialized view with the incremental refresh method (REFRESH FAST). Syntax: USING <INDEX | NO INDEX>
Using Trusted Constraints		Enables creation of a materialized view on top of a table that has a non-NULL Virtual Private Database (VPD) policy on it. It ensures that the materialized view behaves correctly. Materialized view results are computed based on the rows and columns filtered by VPD policy. Therefore, you must coordinate the materialized view definition with the VPD policy to ensure the correct results. Without the USING TRUSTED CONSTRAINTS clause, any VPD policy on a master table will prevent a materialized view from being created. Caution: The USING TRUSTED CONSTRAINTS clause lets Oracle Database use dimension and constraint information that has been declared trustworthy by the database administrator but that has not been validated by the database. If the dimension and constraint information is valid, then performance may improve. However, if this information is invalid, then the refresh procedure may corrupt the materialized view even though it returns a success status. Syntax: USING TRUSTED CONSTRAINTS
Create Materialized View
Fast Refresh On Commit		CREATE MATERIALIZED VIEW <schema.name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> BUILD IMMEDIATE REFRESH <FAST | FORCE> ON <COMMIT | DEMAND> <USING INDEX | USING NO INDEX> INITRANS <integer> STORAGE CLAUSE AS (<SQL statement>);
		conn uwclass/uwclass@pdbdev CREATE MATERIALIZED VIEW mv_simple TABLESPACE uwdata BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM servers; -- create refresh log then repeat (Click Here) CREATE MATERIALIZED VIEW mv_simple TABLESPACE uwdata BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM servers; desc user_snapshots SELECT name, table_name, updatable, refresh_method, refresh_mode FROM user_snapshots; set long 100000 SELECT name, query FROM user_snapshots; SELECT name, last_refresh FROM user_mview_refresh_times;
Fast Refresh On Demand		CREATE MATERIALIZED VIEW <schema.name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> BUILD IMMEDIATE REFRESH <FAST | FORCE> ON <COMMIT | DEMAND> AS (<SQL statement>);
		conn uwclass/uwclass@pdbdev CREATE MATERIALIZED VIEW mv_demand TABLESPACE uwdata NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH FAST ON DEMAND WITH ROWID AS SELECT * FROM servers; desc mv_demand SELECT name, table_name, updatable, refresh_method, refresh_mode FROM user_snapshots; -- refresh is implemented with DBMS_MVIEW.REFRESH. Follow the link below.
Force Refresh		CREATE MATERIALIZED VIEW <schema.name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> BUILD IMMEDIATE REFRESH <FAST | FORCE> ON <COMMIT | DEMAND> AS (<SQL statement>);
		conn uwclass/uwclass@pdbdev CREATE MATERIALIZED VIEW mv_force TABLESPACE uwdata NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH FORCE ON DEMAND WITH ROWID AS SELECT * FROM servers;
Force Refresh Using Trusted Constraints Required for Sync Refresh		CREATE MATERIALIZED VIEW <schema.name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> BUILD IMMEDIATE REFRESH <FAST | FORCE> ON <COMMIT | DEMAND> USING TRUSTED CONSTRAINTS AS (<SQL statement>);
		conn uwclass/uwclass@pdbdev CREATE MATERIALIZED VIEW mv_force TABLESPACE uwdata NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH FORCE ON DEMAND USING TRUSTED CONSTRAINTS WITH ROWID AS SELECT * FROM servers;
Complete Refresh		CREATE MATERIALIZED VIEW <schema.name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> REFRESH <COMPLETE | FORCE> START WITH <date> NEXT <date_calculation> [FOR UPDATE] AS (<SQL statement>);
		conn uwclass/uwclass@pdbdev CREATE MATERIALIZED VIEW mv_complete TABLESPACE uwdata REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT s.srvr_id, i.installstatus, COUNT(*) FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id GROUP BY s.srvr_id, i.installstatus;
Complete Refresh Using Index		CREATE MATERIALIZED VIEW <schema.name> [LOGGING] [CACHE] PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> USING INDEX REFRESH <COMPLETE | FORCE> START WITH <date> NEXT <date_calculation> [FOR UPDATE] AS (<SQL statement>);
		conn uwclass/uwclass@pdbdev CREATE MATERIALIZED VIEW mv_w_index LOGGING CACHE PCTFREE 0 PCTUSED 99 TABLESPACE uwdata USING INDEX REFRESH COMPLETE AS SELECT s.srvr_id, COUNT(*) FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id GROUP BY s.srvr_id;
Prebuilt Table		CREATE MATERIALIZED VIEW <schema.name> ON PREBUILT TABLE PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> REFRESH <COMPLETE | FORCE> START WITH <date> NEXT <date_calculation> [FOR UPDATE] AS (<SQL statement>);
		conn sh/sh@pdborcl CREATE TABLE mv_prebuilt ( month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2)); CREATE MATERIALIZED VIEW mv_prebuilt ON PREBUILT TABLE WITH REDUCED PRECISION AS SELECT t.calendar_month_desc AS month, c.cust_state_province AS state, SUM(s.amount_sold) AS sales FROM times t, customers c, sales s WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id GROUP BY t.calendar_month_desc, c.cust_state_province; SELECT name, table_name, refresh_method, refresh_mode, prebuilt FROM user_snapshots; DROP materialized view mv_prebuilt; desc mv_prebuilt DROP TABLE mv_prebuilt;
Enable Query Rewrite		CREATE MATERIALIZED VIEW <schema.name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> REFRESH <COMPLETE | FORCE> <ENABLE | DISABLE> QUERY REWRITE START WITH <date> NEXT <date_calculation> [FOR UPDATE] AS (<SQL statement>);
		conn uwclass/uwclass@pdbdev set linesize 121 col name format a30 col value format a30 SELECT name, value FROM gv$parameter WHERE name LIKE '%rewrite%'; EXPLAIN PLAN FOR SELECT s.srvr_id, i.installstatus, COUNT(*) FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id AND s.srvr_id = 502 GROUP BY s.srvr_id, i.installstatus; SELECT * FROM TABLE(dbms_xplan.display); CREATE MATERIALIZED VIEW mv_rewrite TABLESPACE uwdata REFRESH ON DEMAND ENABLE QUERY REWRITE AS SELECT s.srvr_id, i.installstatus, COUNT(*) FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id GROUP BY s.srvr_id, i.installstatus;
Alter Materialized View
Allocate Extent		ALTER MATERIALIZED VIEW <schema.materialized_view> ALLOCATE EXTENT (SIZE <size_clause> DATAFILE <'file_name'>) INSTANCE <integer>;
		ALTER MATERIALIZED VIEW mv_simple ALLOCATE EXTENT (SIZE 64K);
Caching		ALTER MATERIALIZED VIEW <schema.materialized_view> <CACHE | NOCACHE>;
		ALTER MATERIALIZED VIEW mv_simple CACHE;
Coalesce		ALTER MATERIALIZED VIEW <schema.materialized_view> <index_organized_table_clause> <alter_overflow_clause> <alter_mapping_table_clause> COALESCE;
		See IOT Library page linked at page bottom
Compile		ALTER MATERIALIZED VIEW <schema.materialized_view> COMPILE;
		ALTER MATERIALIZED VIEW mv_simple COMPILE;
Consider Fresh		ALTER MATERIALIZED VIEW <schema.materialized_view> CONSIDER FRESH;
		ALTER MATERIALIZED VIEW mv_complete CONSIDER FRESH;
Deallocate Unused		ALTER MATERIALIZED VIEW <schema.materialized_view> DEALLOCATE UNUSED;
		ALTER MATERIALIZED VIEW mv_complete DEALLOCATE UNUSED;
LOB Storage		ALTER MATERIALIZED VIEW <schema.materialized_view> LOB (lob_item) STORE AS (lob_storage_parameters);
		See LOBs linked at page bottom
Logging		ALTER MATERIALIZED VIEW <schema.materialized_view> <LOGGING | NOLOGGING>; -- if you have done as recommended here in the Library, use FORCE LOGGING this clause can be ignored
		ALTER MATERIALIZED VIEW mv_simple LOGGING;
Modify LOB Storage		ALTER MATERIALIZED VIEW <schema.materialized_view> MODIFY LOB (<lob_item>) (new_lob_parameter);
		See LOBs linked at page bottom
Parallel Access		ALTER MATERIALIZED VIEW <schema.materialized_view> <PARALLEL | NO_PARALLEL>;
		ALTER MATERIALIZED VIEW mv_simple PARALLEL;
Physical Attributes		ALTER MATERIALIZED VIEW <schema.materialized_view> ( PCT_FREE <integer> PCT_USED <integer> INITRANS <integer> TABLESPACE <tablespace_name>;
		ALTER MATERIALIZED VIEW mv_simple PCTFREE 1;
Query Rewrite		ALTER MATERIALIZED VIEW <schema.materialized_view> <ENABLE | DISABLE> QUERY REWRITE;
		ALTER MATERIALIZED VIEW mv_simple ENABLE QUERY REWRITE;
Refresh		ALTER MATERIALIZED VIEW <schema.materialized_view> REFRESH <FAST | COMPLETE | FORCE> ON <DEMAND | COMMIT> START WITH <date_time> NEXT <date_time> WITH PRIMARY KEY USING DEFAULT MASTER ROLLBACK SEGMENT USING <ENFORCED | TRUSTED> CONSTRAINTS;
		ALTER MATERIALIZED VIEW mv_complete REFRESH COMPLETE;
Shrink		ALTER MATERIALIZED VIEW <schema.materialized_view> SHRINK SPACE <COMPACT | CASCADE>;
		ALTER MATERIALIZED VIEW mv_simple ENABLE ROW MOVEMENT; ALTER MATERIALIZED VIEW mv_simple SHRINK SPACE CASCADE;
Table Compression		ALTER MATERIALIZED VIEW <schema.materialized_view> <COMPRESS | NOCOMPRESS>;
		ALTER MATERIALIZED VIEW mv_simple COMPRESS;
Table Partitioning		ALTER MATERIALIZED VIEW <schema.materialized_view> ....
		See Partitioning Library Page
Drop
Drop Materialized View		DROP MATERIALIZED VIEW <schema.materialized_view>;
		SELECT table_name FROM user_tables; DROP MATERIALIZED VIEW mv_simple;
Drop Snapshot		DROP MATERIALIZED VIEW <schema.materialized_view>;
		SELECT table_name FROM user_tables; DROP SNAPSHOT mv_complex;
Drop Materialized View Preserve Table		DROP MATERIALIZED VIEW <schema.materialized_view> PRESERVE TABLE;
		SELECT table_name FROM user_tables; DROP MATERIALIZED VIEW mv_simple PRESERVE TABLE;
Create Refresh Log
Create Fast Refresh Log Table		CREATE MATERIALIZED VIEW LOG ON <schema.table_name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> <LOGGING | NOLOGGING> <CACHE | NOCACHE> <NOPARALLEL | PARALLEL <integer>> <table_partitioning_clause> WITH <OBJECT | PRIMARY KEY | ROWID | SEQUENCE | (column_list)> [<INCLUDING | EXCLUDING> NEW VALUES];
		conn uwclass/uwclass@pdbdev CREATE MATERIALIZED VIEW LOG ON servers PCTFREE 0 PCTUSED 99 TABLESPACE uwdata WITH PRIMARY KEY, ROWID, SEQUENCE; CREATE MATERIALIZED VIEW mv_simple TABLESPACE uwdata BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM servers;
Include a base table column in the materialized view log		CREATE MATERIALIZED VIEW LOG ON servers PCTFREE 0 PCTUSED 99 TABLESPACE uwdata WITH SEQUENCE, (LATITUDE, LONGITUDE);
Include new value in the MV log		CREATE MATERIALIZED VIEW LOG ON servers PCTFREE 0 PCTUSED 99 TABLESPACE uwdata WITH SEQUENCE, (LATITUDE, LONGITUDE) INCLUDING NEW VALUES;
Alter Refresh Log
Add Clause		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> ADD <OBJECT | PRIMARY KEY | ROWID | SEQUENCE | (column_list)> [<INCLUDING | EXCLUDING> NEW VALUES];
		ALTER MATERIALIZED VIEW LOG ON servers ADD SEQUENCE;
Alter Physical Attributes		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name>;
		SELECT table_name, pct_free, pct_used FROM user_tables; ALTER MATERIALIZED VIEW LOG ON servers PCTFREE 20; SELECT table_name, pct_free, pct_used FROM user_tables;
Alter Parallel Access		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> <NOPARALLEL | PARALLEL <integer>>;
		SELECT table_name, degree FROM user_tables; ALTER MATERIALIZED VIEW LOG ON servers PARALLEL 8; SELECT table_name, degree FROM user_tables;
Alter Logging Clause		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> <LOGGING | NOLOGGING>;
		ALTER MATERIALIZED VIEW LOG ON servers LOGGING;
Alter Allocate Extent by Size		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> ALLOCATE EXTENT (SIZE <integer> <M | G | T>);
		ALTER MATERIALIZED VIEW LOG ON servers ALLOCATE EXTENT (SIZE 512K);
Alter Allocate Extent by Datafile		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> ALLOCATE EXTENT (DATAFILE <file_name>);
		ALTER MATERIALIZED VIEW LOG ON servers ALLOCATE EXTENT (DATAFILE 'u01/apps/oracle/oradata/orabase/pdbdev/uwdataq01.dbf');
Alter Allocate Extent by Instance		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> ALLOCATE EXTENT SIZE (INSTANCE <integer>);
		ALTER MATERIALIZED VIEW LOG ON servers ALLOCATE EXTENT (INSTANCE 1);
Log Caching		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> <CACHE | NOCACHE>;
		ALTER MATERIALIZED VIEW LOG ON servers CACHE;
Shrink Log		ALTER MATERIALIZED VIEW LOG [FORCE] ON <schema.table_name> [COMPACT] [CASCADE];
		ALTER MATERIALIZED VIEW LOG ON servers SHRINK SPACE COMPACT CASCADE; ALTER TABLE mlog$_servers ENABLE ROW MOVEMENT; ALTER MATERIALIZED VIEW LOG ON servers SHRINK SPACE COMPACT CASCADE;
Truncate Refresh Log
MV Log Purging does not shrink the logs. This technique shrinks them though it does require obtaining an exclusive lock on the base table.		Session 1 Session 2 conn uwclass/uwclass@pdbdev SELECT usl.master, usl.log_table, us.blocks FROM user_snapshot_logs usl, user_segments us WHERE usl.log_table = us.segment_name; LOCK TABLE servers IN EXCLUSIVE MODE; CREATE TABLE mvlog_temp AS  SELECT * FROM mlog$_servers; conn uwclass/uwclass@pdbdev   TRUNCATE TABLE mlog$_servers; INSERT INTO mlog$_servers SELECT * FROM mvlog_temp; ROLLBACK; SELECT usl.master, usl.log_table, us.blocks FROM user_snapshot_logs usl, user_segments us WHERE usl.log_table = us.segment_name;
Drop Refresh Log
Drop Log		DROP MATERIALIZED VIEW LOG ON <table_name>;
		DROP MATERIALIZED VIEW LOG ON servers;
Addendum
To determine if a materialized view is in the process of being refreshed		SELECT 1 FROM gv$mvrefresh WHERE currmvowner = 'UWCLASS' AND currmvname = 'MV_COMPLETE';