Materialized Views
Materialized views are database objects that store the results of a query physically, unlike regular
views which are virtual and execute the underlying query each time they're accessed. This makes
them particularly useful for:
Improving query performance on large datasets
Replicating data across distributed systems
Pre-computing complex aggregations and joins
After the MV is initially populated, at some later point in time you can re-run the MV query and
store the fresh results in the underlying table.
The MV can be a query based on tables, views, and other materialized views. The base tables are
often referred to as master tables.
Common Use Cases
Data warehousing and business intelligence
Complex reporting systems
Applications requiring frequent aggregations
Distributed database environments
Real-time analytics on large datasets
Creating Basic Materialized Views
The fundamental syntax for creating a materialized view
CREATE MATERIALIZED VIEW view_name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE]
ON [COMMIT | DEMAND]
[ENABLE | DISABLE] QUERY REWRITE
AS
SELECT ...;
Key Clauses Explained:
[Link] IMMEDIATE/DEFERRED:
IMMEDIATE: Populates the view immediately (default)
DEFERRED: Populates on first refresh request
[Link] Options:
FAST: Uses materialized view logs for incremental refresh. Process during which only
DML changes that have occurred since the last refresh are applied to an MV. Materialized
view log is Database object that tracks DML changes to the MV base table. An MV log is
required for fast refreshes. It can be based on the primary key, ROWID, or object ID.
COMPLETE: Re-executes the entire query to refresh. Process in which an MV is deleted
from and completely refreshed with an MV SQL statement.
FORCE: Attempts fast refresh first, falls back to complete if not possible (default)
[Link] Timing:
ON COMMIT: Refreshes automatically when source data changes
ON DEMAND: Requires manual refresh (default)
[Link] REWRITE:
Allows optimizer to use the materialized view for query optimization To create
materialized views, you need:
CREATE MATERIALIZED VIEW privilege
CREATE TABLE or CREATE ANY TABLE privilege
Access privileges on the base tables
Here's a basic example that creates a materialized view from a single table. This creates a complete
copy of the employees table that can be refreshed manually:
CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
AS
SELECT * FROM employees;
To enable fast refreshes1, you need materialized view logs on the base tables. These logs track
changes to the base tables, allowing incremental refreshes. Here are the steps for implementing a
fast refreshable MV:
1. Create a base table (if it has not already been created).
2. Create an MV log on the base table.
3. Create an MV as fast refreshable.
When a fast refresh occurs, the MV log must have a unique way to identify which records have
been modified and thus need to be refreshed. You can do this with two different approaches. One
method is to specify the PRIMARY KEY clause when you create the MV log; the other is to specify
the ROWID clause. If the underlying base table has a primary key, then use the primary key–based
MV log. If the underlying base table has no primary key, then you have to create the MV log, using
ROWID. In most cases, you will probably have a primary key defined for every base table.
However, the reality is that some systems are poorly designed or have some rare reason for a table
not to have a primary key.
In this example, a primary key is defined on the base table, so you create the MV log with the
PRIMARY KEY clause:
create materialized view log on employees with primary key;
When you use a primary key-based fast refreshable MV, the primary key column(s) of the base
table must be part of the fast refreshable MV SELECT statement.
create materialized view emp_mv
segment creation immediate
refresh
with primary key
fast
on demand
as
select employee_id, salary, trunc(hire_date) hire_date
from employees;
Manual refresh can be done using:
BEGIN
DBMS_MVIEW.REFRESH('emp_mv');
END;
/
Or for multiple views:
BEGIN
DBMS_MVIEW.REFRESH('emp_mv, dept_emp_mv');
END;
/
This example passes two parameters to the REFRESH procedure: the name and the refresh method.
The name is EMP_MV, and the parameter is F (for fast):
SQL> exec dbms_mview.refresh('EMP_MV','F');
To initiate a complete refresh the parameter passed in is C (for complete):
SQL> exec dbms_mview.refresh('SALES_MV','C');
1 Not supported in Oracle 11g xe.
Best Practices
[Link] NOLOGGING option for large materialized views to reduce redo generation.
[Link] statistics after creating materialized views:
BEGIN
DBMS_STATS.gather_table_stats('SCHEMA_NAME', 'MV_NAME');
create materialized view emp_det_mv
segment creation immediate
refresh
with primary key
fast
on demand
as
select employee_id, salary, trunc(hire_date) hire_date
from employees;END;
[Link] partitioning large materialized views for better manageability.
[Link] aggregation scenarios, enable query rewrite to maximize performance benefits
When working with materialized views in Oracle, it's important to understand the distinction
between underlying tables and master tables:
Master Table (Base Table)
Definition: The original source table(s) from which the materialized view derives its data
Primary Role: Contains the authoritative, up-to-date data that the materialized view will
replicate or summarize. Any changes to these tables may trigger refreshes of dependent
materialized views
Underlying Table (Storage Table)
Definition: The actual physical table that stores the materialized view's data
Primary Role: Persistently stores the pre-computed results of the materialized view's query
Characteristics:
Created automatically when you create the materialized view
Behaves like a regular table in the database (has storage, indexes, etc.)
Contains the snapshot or aggregated data from the master table(s)
Has a direct one-to-one relationship with the materialized view object
Key Differences
Aspect Master Table Underlying Table (of Materialized View)
Data Freshness Always current Potentially stale until refreshed
Storage Location Original database/schema Local to the materialized view
Update Behavior Direct DML operations allowed Read-only (updated only via refresh)
Purpose Source of truth Performance optimization cache
Dependencies Independent Depends on master table(s)
-- Master table (source of truth)
CREATE TABLE sales_data (
sale_id NUMBER PRIMARY KEY,
product_id NUMBER,
sale_date DATE,
amount NUMBER(10,2)
);
-- Materialized view with its own underlying storage
CREATE MATERIALIZED VIEW daily_sales_mv
REFRESH COMPLETE ON DEMAND
AS
SELECT TRUNC(sale_date) AS day,
SUM(amount) AS daily_total
FROM sales_data
GROUP BY TRUNC(sale_date);
In this case:
sales_data is the master table
Oracle automatically creates an underlying table (with a system-generated name) to store the
grouped data
The underlying table contains the pre-aggregated daily totals
TRUNC(sale_date) is an Oracle SQL function that truncates (cuts off) the time portion from a
DATE value, returning just the date component. It's commonly used in materialized views and other
database operations when you want to group or compare dates without considering the time
elements.
Example:
If sale_date contains '2023-10-15 14:35:22':
SELECT TRUNC(sale_date) FROM dual;
Every MV has an underlying tables associated with it.
A common task involves adding a column to or dropping a column from a base table (because
business requirements have changed). After the column is added to or dropped from the base table,
you want those DDL changes to be reflected in any dependent Mvs.
Note:- To modify an MV, you have to change the SQL query that the MV is based on. Because
there is no ALTER MATERIALIZED VIEW ADD/DROP/MODIFY <column> statement, you must
do the following to add/delete columns in an MV:
1. Alter the base table.
2. Drop and re-create the MV to reflect the changes in the base table.
Suppose you make a modification to a base table, such as adding a column:
SQL> alter table inv add(inv_loc varchar2(30));
You also have a simple MV named INV_MV that is based on this table. You want the base-table
modification to be reflected in the MV.
Drop and re-create the MV to include the column definition:
drop materialized view inv_mv;
create materialized view inv_mv
refresh fast on demand
as
select inv_id, inv_desc, inv_loc
from inv;
This approach may take a long time if large amounts of data are involved. You have downtime for
any application that accesses the MV while it’s being rebuilt. If you work in a large data-warehouse
environment, then due to the amount of time it takes to completely refresh the MV, you may want to
consider not dropping the underlying table.
Altering a Materialized View but Preserving the Underlying Table
When you drop an MV, you have the option of preserving the underlying table and its data. You
may find this approach advantageous when you’re working with large MVs in data-warehouse
environments.
Here are the steps:
1. Alter the base table.
2. Drop the MV, but preserve the underlying table.
3. Modify the underlying table.
4. Re-create the MV using the ON PREBUILT TABLE clause.
Here’s a simple example to illustrate this procedure:
SQL> alter table inv add(inv_loc varchar2(30));
Drop the MV, but specify that you want to preserve the underlying table:
SQL> drop materialized view inv_mv preserve table;
Now, modify the underlying table:
SQL> alter table inv_mv add(inv_loc varchar2(30));
Next, create the MV using the ON PREBUILT TABLE clause:
create materialized view inv_mv
on prebuilt table
using index tablespace mv_index
as
select inv_id, inv_desc, inv_loc
from inv;
This allows you to redefine the MV without dropping and completely refreshing the data.
Be aware that if there is any Data Manipulation Language (DML) activity against the base table
during the MV rebuild operation, those transactions aren’t reflected in the MV when you attempt to
refresh it. In data warehouse environments, you typically have a known schedule for loading base
tables and therefore should be able to schedule the MV alteration during a maintenance window
when no transactions are occurring in the base table.