Oracle Materialized Views

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

Privileges required

create materialized view
create any materialized view
drop any materialized view
delete any table
insert any table
lock any table
select any table
under any table
update any table
create table
create view

Syntax (Fast Refresh)

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

Example:

CREATE MATERIALIZED VIEW mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT * FROM servers;

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

Example:

CREATE MATERIALIZED VIEW mv_force
TABLESPACE uwdata
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH ROWID AS
SELECT * FROM servers;

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

Example:

CREATE MATERIALIZED VIEW mv_complete
TABLESPACE uwdata
REFRESH COMPLETESTART 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;

Syntax (Complete Refresh Using Index)

CREATE MATERIALIZED VIEW <schema.name>
[LOGGING] [CACHE]
PCTFREE <integer>
PCTUSED <integer>
USING INDEX
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

Example:

CREATE SNAPSHOT mv_w_index
LOGGING CACHE
PCTFREE 0 PCTUSED 99
TABLESPACE uwdata
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;

Syntax (Prebuilt Table)

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

Example:

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;

Syntax (Enable Query Rewrite)

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

Example:
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;