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

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.

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

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