Fast Refreshable Materialized View

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query


Simple Materialized View
Appropriate for Materialized View’s where the base tables have a small amount of changes over a period of time compared to the total number of rows in the table.
1. Create the Materialized View log.
2. Create the Materialized View as Fast Refreshable.

A Fast Refreshable Materialized View requires a Materialized View Log on the base table(s).
SQL> create materialized view log on experiment_runs;

select object_name, object_type
from user_objects where object_name like '%EXPERIMENT%';

RUPD$_EXPERIMENT_RUNS    TABLE
MLOG$_EXPERIMENT_RUNS    TABLE

create materialized view EXPERIMENTS_MV
refresh
with primary key
fast
on commit
as
select EXP_RUNID, USERNAME
from EXPERIMENT_RUNS
where SUCCESS = 'Y'
order by USERNAME;

select object_name, object_type
from user_objects where object_name like '%EXPERIMENT%';

RUPD$_EXPERIMENT_RUNS    TABLE
MLOG$_EXPERIMENT_RUNS    TABLE
EXPERIMENTS_MV        MATERIALIZED VIEW
EXPERIMENTS_MV        TABLE
Primary Key VS. ROWID
SQL> create materialized view log on experiment_runs with primary key; (This is the default).
SQL> create materialized view log on experiment_runs with rowid; (Required if there is no PK on base table).

The Primary Key parameter also applies to the Materialized View itself. The ‘with primary key’ parameter is the default option and if no primary key exists, the Materialized View must include the ‘with rowed’ parameter.

When you use create a Primary Key-Fast Refreshable Materialized View, the Primary Key MUST be part of the Materialized View select clause.

Simple Materialized View VS. Complex Materialized View
Simple Materialized View can always be Fast Refreshed. Based on a single table (usually). It is possible to create a Simple Materialized View on a complex query (2 tables).

Complex Materialized View cannot be Fast Refreshed. This includes queries with more than one table, aggregates, and functions. You can still create a Materialized View that is Complete Refresh on Commit.

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query