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 91011121314151617

-- Standard CreationCREATE MATERIALIZED VIEW view_nameBUILD [IMMEDIATE | DEFERRED]REFRESH [FAST | COMPLETE | FORCE]ON [COMMIT | DEMAND][[ENABLE | DISABLE] QUERY REWRITE]ASSELECT ...;-- With Pre-Built TableCREATE MATERIALIZED VIEW view_nameON PREBUILT TABLEREFRESH [FAST | COMPLETE | FORCE]ON [COMMIT | DEMAND][[ENABLE | DISABLE] QUERY REWRITE]ASSELECT ...;

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 910111213141516171819202122232425

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