Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Oracle Materialized View Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Materi­alized View

Simplest way to replicate data between sites or transform data format
grant CREATE MATERI­ALIZED VIEW system privilege
grant CREATE TABLE or CREATE ANY TABLE system privilege

Syntax

CREATE MATERI­ALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED] or ON PREBUILT TABLE
affect 1st populate data
REFRESH [FAST | COMPLETE | FORCE ]
fast need logs present, complete truncate the table
ON [COMMIT | DEMAND ]
commit rely on source data change;
[[ENABLE | DISABLE] QUERY REWRITE]
AS SELECT ...;

Gether stats

exec  DBMS_STATS.gather_table_stats(
    ownname => 'SCOTT',
    tabname => 'EMP_MV');

Create Materi­alized View Log

CREATE MATERIALIZED VIEW LOG 
ON scott.emp
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;
used for fast refresh
  

Refresh Materi­alized Views

--only when ON DEMAND is used
EXEC DBMS_MVIEW.refresh('EMP_MV');
--regular refresh with oracle scheduler
using oracle scheduler call  DBMS_MVIEW.refresh
--regular refresh with dbms_refresh package
use dbms_refresh.make then add

BEGIN
   DBMS_REFRESH.make(
     name                 => 'SCOTT.MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/1:Mins/ SYSDATE + 1/(60*24)',);
END;

BEGIN
   DBMS_REFRESH.add(
     name => 'SCOTT.MINUTE_REFRESH',
     list => 'SCOTT.EMP_MV',
     lax  => TRUE);
END;

Consid­era­tions

populate and refresh MV add additional loads
if not regular refreshed, MV logs are not necessary
fast refreshes may not be possible, due to Oracle version and the complexity of the associated query
QUERY_­­RE­W­R­IT­­E_I­­NT­E­GRITY and QUERY_­­RE­W­R­IT­­E_E­­NABLED parameter need to be set for query rewrite