šŸ§  Second Brain

Search

Search IconIcon to open search

Materialized Views

Last updated Feb 9, 2024

A materialized view is a database object containing the results of a SQL query. The concept is also known as “Query rewrite”. It might be a local copy of remotely located data, a subset of a tableā€™s rows and/or columns, a join result, or a summary created using an aggregate function.

When a materialized view is executed, the SQL query is persisted in storage, enabling a fast query response.

Materialized Views (MVs) can be likened to Data Marts, One Big Table, or executed dbt models (dbt).

# History

So you see, the terms are coming back. The Oracle Database first implementedĀ ==materialized==Ā ==view==s in version 8i (1998). They were added later by Postgres and SQLServer.

In April 2020, even GoogleĀ  announcedĀ April 2020Ā ==materialized==Ā ==view==s capabilities in BigQuery. Check Materialized Views in Google BigQuery.

# Oracle Syntax

# Creating a Materialized View

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
-- Standard Creation
CREATE MATERIALIZED VIEW view_name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE]
ON [COMMIT | DEMAND]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

-- With Pre-Built Table
CREATE MATERIALIZED VIEW view_name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE]
ON [COMMIT | DEMAND]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

# Refreshing Materialized Views

If a materialized view is set to refresh on commit, manual refreshes are typically unnecessary, except when a rebuild is required. Remember, refreshing on commit can be resource-intensive for volatile base tables. Fast refreshes are generally preferable.

For on-demand refreshes, either manual refresh or inclusion in a refresh group is possible.

The code below demonstrates creating a refresh group scheduled to refresh every minute, and adding a materialized view to it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
BEGIN
   DBMS_REFRESH.make(
     name                 => 'SCOTT.MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/

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

Source


Origin: The first technique I used in the Oracle database in 2008.
References: One Big Table (OBT) or Snapshotting or Materialized Views
Created 2023-12-06