Oracle | Materialized Views

Oracle Materialized Views Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose 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.

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.

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
            DBA_REGISTERED_SNAPSHOT_GROUPS
            SNAP$
          
          
            CDB_REFRESH_DEPENDENCIES
            GV$MREFRESH
            TAB$
          
          
            COL$
            SLOG$
            USER_REFRESH_DEPENDENCIES
          
        


      -- plus the objects returned by the following query

      SELECT view_name

      FROM dba_views

      WHERE view_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>

Evaluate Edition Specifies the editions in which the materialized view is enabled for query rewrite.

Syntax: EVALUATE USING <CURRENT EDITION | EDITION <edition_name> | NULL EDITION>

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.

Unusable Editions Specifies the editions in which the materialized view is not enabled for query rewrite.

Syntax: UNUSABLE BEFORE <CURRENT EDITION | EDITION <edition_name>>

Syntax: UNUSABLE BEGINNING WITH <CURRENT EDITION | EDITION <edition_name> | NULL EDITION>

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

Note: Jonathan Lewis has indicated that REFRESH ON COMMIT with a single row update can cause 45 statement executions. Also be wary of possible read consistency violations prior to the commit. Whether this behaviour still exists in 12c is not tested at this time. CREATE MATERIALIZED VIEW [schema_name.]<mv_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;

      

      SELECT table_name

      FROM user_tables;

      

      SELECT constraint_name, table_name, constraint_type

      FROM user_constraints;

      

      CREATE OR REPLACE VIEW servers_view AS

      SELECT * FROM servers;

      

      desc servers

      desc servers_view

      desc mv_simple

      

      SELECT DISTINCT network_id

      FROM servers;

      

      SELECT DISTINCT network_id

      FROM servers_view;

      

      SELECT DISTINCT network_id

      FROM mv_simple;

      

      UPDATE servers

      SET network_id = 10

      WHERE network_id = 6;

      

      SELECT DISTINCT network_id

      FROM servers;

      

      SELECT DISTINCT network_id

      FROM servers_view;

      

      SELECT DISTINCT network_id

      FROM mv_simple;

      

      COMMIT;

      

      SELECT DISTINCT network_id

      FROM servers;

      

      SELECT DISTINCT network_id

      FROM servers_view;

      

      SELECT DISTINCT network_id

      FROM mv_simple;

Fast Refresh On Demand CREATE MATERIALIZED VIEW [schema_name.]<mv_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;

      

      set long 10000

      

      SELECT name, query

      FROM user_snapshots;

      

      -- refresh is implemented with DBMS_MVIEW.REFRESH. Follow the link below.

Force Refresh CREATE MATERIALIZED VIEW [schema_name.]<mv_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;

      

      desc mv_force

      

      SELECT name, table_name, updatable, refresh_method,

      refresh_mode

      FROM user_snapshots;

      

      set long 10000

      

      SELECT name, query

      FROM user_snapshots;

      

      -- refresh is implemented with DBMS_MVIEW.REFRESH. Follow the link below.

Force Refresh Using Trusted Constraints

Required for Sync Refresh CREATE MATERIALIZED VIEW [schema_name.]<mv_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;

      

      desc mv_force

      

      SELECT name, table_name, updatable, refresh_method,

      refresh_mode

      FROM user_snapshots;

      

      set long 10000

      

      SELECT name, query

      FROM user_snapshots;

      

      -- refresh is implemented with DBMS_MVIEW.REFRESH. Follow the link below.

Complete Refresh CREATE MATERIALIZED VIEW [schema_name.]<mv_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;

Note: To create a materialized view that refreshes at 3:00am in the morning:

SQL> SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS')

              2  FROM dual;

              

              TO_CHAR(SYSDATE,'MM/DD/YYYYHH

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

              12/03/2006 01:25:30

              

              SQL> SELECT TO_CHAR(TRUNC(SYSDATE) + 3/24, 'MM/DD/YYYY HH:MI:SS')

              2  FROM dual;

              

              TO_CHAR(TRUNC(SYSDATE)+3/24,'M

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

              12/03/2006 03:00:00

            

            To create one that refreshes every day at 06:00 and 18:00

            

            NEXT case

              when to_number(to_char(sysdate,'hh24'))<6 then trunc(sysdate)+1/4

              when to_number(to_char(sysdate,'hh24'))<18 then trunc(sysdate)+3/4

              else trunc(sysdate)+5/4

            end

SELECT name, table_name, updatable, refresh_method

      FROM user_snapshots;

      

      SELECT name, table_name,

      refresh_method

      FROM user_snapshots;

      

      col next format a30

      

      SELECT name, type, next, start_with, refresh_group

      FROM user_snapshots;

      

      col query format a50

      

      SELECT name, query, status

      FROM user_snapshots;

      

      SELECT *

      FROM mv_complete;

Complete Refresh Using Index CREATE MATERIALIZED VIEW [schema_name.]<mv_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;

      

      desc mv_w_index

      

      SELECT name, table_name, updatable, refresh_method

      FROM user_snapshots;

      

      SELECT * FROM mv_w_index;

      

      SELECT index_name, index_type

      FROM user_indexes;

      

      SELECT column_expression

      FROM user_ind_expressions

      WHERE table_name = 'MV_W_INDEX';

Prebuilt Table CREATE MATERIALIZED VIEW [schema_name.]<mv_name> ON PREBUILT TABLE [WITH REDUCED PRECISION] PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> REFRESH <COMPLETE | FORCE> START WITH <date> NEXT <date_calculation> [FOR UPDATE] AS (<SQL statement>);

conn sh/sh@pdbdev

      

      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.]<mv_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;

      

      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);

      

      -- if the base table may be updated then

      ALTER SESSION SET query_rewrite_integrity = STALE_TOLERATED;

MV demo by Maria Colgan working with a 2.56 million JSON documents CREATE MATERIALIZED VIEW top_ten_cust_history_mv ENABLE query_rewrite ENABLE on query computation AS SELECT c.c_name, o.order_details.location_city, SUM(TO_NUMBER(o.order_details.salesAmount)) AS TOTAL FROM customers c, orders_history o WHERE c.c_custid = o.orders_history.loyaltyCardNo GROUP BY c.c_name, o.orders_details.location.city;

Alter Materialized View

Allocate Extent ALTER MATERIALIZED VIEW [schema_name.]<mv_name> ALLOCATE EXTENT (SIZE <size_clause> DATAFILE <'file_name'>) INSTANCE <integer>;

ALTER MATERIALIZED VIEW mv_simple ALLOCATE EXTENT (SIZE 64K);

Caching ALTER MATERIALIZED VIEW [schema_name.]<mv_name> <CACHE | NOCACHE>;

ALTER MATERIALIZED VIEW mv_simple CACHE;

Coalesce ALTER MATERIALIZED VIEW [schema_name.]<mv_name> <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_name.]<mv_name> COMPILE;

ALTER MATERIALIZED VIEW mv_simple COMPILE;

Consider Fresh ALTER MATERIALIZED VIEW [schema_name.]<mv_name> CONSIDER FRESH;

ALTER MATERIALIZED VIEW mv_complete CONSIDER FRESH;

Deallocate Unused ALTER MATERIALIZED VIEW [schema_name.]<mv_name> DEALLOCATE UNUSED;

ALTER MATERIALIZED VIEW mv_complete DEALLOCATE UNUSED;

LOB Storage ALTER MATERIALIZED VIEW [schema_name.]<mv_name> LOB (lob_item) STORE AS (lob_storage_parameters);

See LOBs linked at page bottom

Logging ALTER MATERIALIZED VIEW [schema_name.]<mv_name> <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_name.]<mv_name> MODIFY LOB (<lob_item>) (new_lob_parameter);

See LOBs linked at page bottom

Parallel Access ALTER MATERIALIZED VIEW [schema_name.]<mv_name> <PARALLEL | NO_PARALLEL>;

ALTER MATERIALIZED VIEW mv_simple PARALLEL;

Physical Attributes ALTER MATERIALIZED VIEW [schema_name.]<mv_name> ( PCT_FREE <integer> PCT_USED <integer> INITRANS <integer> TABLESPACE <tablespace_name>;

ALTER MATERIALIZED VIEW mv_simple PCTFREE 1;

Query Rewrite ALTER MATERIALIZED VIEW [schema_name.]<mv_name> <ENABLE | DISABLE> QUERY REWRITE;

ALTER MATERIALIZED VIEW mv_simple ENABLE QUERY REWRITE;

Refresh ALTER MATERIALIZED VIEW [schema_name.]<mv_name> 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_name.]<mv_name> 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_name.]<mv_name> <COMPRESS | NOCOMPRESS>;

ALTER MATERIALIZED VIEW mv_simple COMPRESS;

Table Partitioning ALTER MATERIALIZED VIEW [schema_name.]<mv_name> ....

See Partitioning Library Page

Drop

Drop Materialized View DROP MATERIALIZED VIEW [schema_name.]<mv_name>;

SELECT table_name

      FROM user_tables;

      

      DROP MATERIALIZED VIEW mv_simple;

      

      SELECT table_name

      FROM user_tables;

Drop Snapshot DROP MATERIALIZED VIEW [schema_name.]<mv_name>;

SELECT table_name

      FROM user_tables;

      

      DROP SNAPSHOT mv_complex;

      

      SELECT table_name

      FROM user_tables;

Drop Materialized View Preserve Table DROP MATERIALIZED VIEW [schema_name.]<mv_name> PRESERVE TABLE;

SELECT table_name

      FROM user_tables;

      

      DROP MATERIALIZED VIEW mv_simple  PRESERVE TABLE;

      

      SELECT table_name

      FROM user_tables;

      

      DROP TABLE mv_serers;

      

      SELECT table_name

      FROM user_tables;

Create Refresh Log

conn uwclass/uwclass@pdbdev

      

      CREATE MATERIALIZED VIEW LOG ON servers

      PCTFREE 0 PCTUSED 99

      TABLESPACE uwdata

      WITH PRIMARY KEY, ROWID, SEQUENCE;

      

      desc user_snapshot_logs

      

      SELECT master, log_table

      FROM user_snapshot_logs;

      

      desc user_mview_logs

      

      SELECT master, log_table

      FROM user_mview_logs;

      

      SELECT master, log_table, rowids, primary_key

      FROM user_mview_logs;

      

      SELECT table_name

      FROM user_tables;

      

      desc mlog$_servers

      

      SELECT * FROM mlog$_servers;

      

      desc rupd$_servers

      

      SELECT * FROM rupd$_servers;

      

      INSERT INTO servers

      (srvr_id)

      VALUES

      (99999);

      

      SELECT * FROM mlog$_servers;

      

      SELECT * FROM rupd$_servers;

      

      COMMIT;

      

      CREATE MATERIALIZED VIEW mv_simple

      TABLESPACE uwdata

      BUILD IMMEDIATE

      REFRESH FAST ON COMMIT AS

      SELECT *
      FROM servers;

      

      SELECT * FROM mlog$_servers;

      

      SELECT * FROM rupd$_servers;

      

      DELETE FROM servers

      WHERE srvr_id = 99999;

      

      SELECT * FROM mlog$_servers;

      

      COMMIT;

      

      SELECT * FROM mlog$_servers;

      

      DELETE servers WHERE srvr_id = 99999;

      

      UPDATE servers

      SET srvr_id = 99999

      WHERE srvr_id = 5;

      

      SELECT * FROM mlog$_servers;

      

      COMMIT;

      

      SELECT * FROM mlog$_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); desc mlog$_servers

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; desc mlog$_servers -- need to write a full demo

Alter Refresh Log

ALTER MATERIALIZED VIEW LOG ON servers ADD SEQUENCE;

Alter Physical Attributes ALTER MATERIALIZED VIEW LOG [FORCE] ON [schema_name.]<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_name.]<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_name.]<table_name> <LOGGING | NOLOGGING>;

ALTER MATERIALIZED VIEW LOG ON servers LOGGING;

Alter Allocate Extent by Size ALTER MATERIALIZED VIEW LOG [FORCE] ON [schema_name.]<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_name.]<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_name.]<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_name.]<table_name> <CACHE | NOCACHE>;

ALTER MATERIALIZED VIEW LOG ON servers CACHE;

Shrink Log ALTER MATERIALIZED VIEW LOG [FORCE] ON [schema_name.]<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.

-- demo based on the mv log created on the table uwclass.servers: above. does this requires two sessions as shown here

      
        
          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 [schema_name.]<table_name>;

DROP MATERIALIZED VIEW LOG ON servers;

Addendum

Indexing [with respect to MV's on 10gR2 Jonathan Lewis wrote ] ... you are allowed to create indexes on the tables that sit under materialized views - just don't make them unique indexes. Verify that what you are doing, in 11gR2 or 12c, is not an issue.

To determine is a materialized view is in the process of being refreshed SELECT 1 FROM gv$mvrefresh WHERE currmvowner = 'UWCLASS' AND currmvname = 'MV_COMPLETE';