SQL - Materialized View

Data System Architecture

About

A materialized view is an auxiliary data structure that persist the result of a query in a table format managed by the cache system of the database.

The refresh of the data is defined in criteria:

A materialized view creates a real table, and this table may be indexed, analyzed, and so on.

When the queries are known in advance and predictable, materialized views are very helpful.

Example

Oracle

create materialized view EmpSummary as
   select deptno, COUNT(*) as c, SUM(sal) as s
   from Emp
   group by deptno

Usage

Materialized Views are used to:

Summary table

Materialized views are the equivalent of a summary table.

In a Online Analytical Processing (Olap) approach, each of the elements of a dimension could be summarized using a hierarchy.

The end user queries the tables and views in the database. The query rewrite mechanism in a database automatically rewrites the SQL query to use this summary tables. See also: Relational Data Modeling - View selection problem (recommending the best aggregation tables) - Data Warehousing

Rewrite

This mechanism reduces response time for returning results from the query. Materialized views within the data warehouse are transparent to the end user or to the database application.

This is relatively straightforward and is answered in a single word - performance. By calculating the answers to the really hard questions up front (and once only), we will greatly reduce the load on the machine, We will experience:

  • Less physical reads - There is less data to scan through.
  • Less writes - We will not be sorting/aggregating as frequently.
  • Decreased CPU consumption - We will not be calculating aggregates and functions on the data, as we will have already done that.
  • Markedly faster response times - Our queries will return incredibly quickly when a summary is used, as opposed to the details. This will be a function of the amount of work we can avoid by using the materialized view, but many orders of magnitude is not out of the question.

Materialized views will increase your need for one resource - more permanently allocated disk. We need extra storage space to accommodate the materialized views, of course, but for the price of a little extra disk space, we can reap a lot of benefit.

Also notice that we may have created a materialized view, but when we ANALYZE, we are analyzing a table. A materialized view creates a real table, and this table may be indexed, analyzed, and so on.

Replica

A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

Database Implementation

Algorithm

Given a database with 30 columns, 10M rows. Find X summary tables with under Y rows that improve query response time the most. AdaptiveMonteCarlo algorithm:

  • Based on research [2]
  • Greedy algorithm that takes a combination of summary tables and tries to find the table that yields the greatest cost/benefit improvement
  • Models “benefit” of the table as query time saved over simulated query load
  • The “cost” of a table is its size





Discover More
Relational Data Model
(Relation|Table) - Tabular data

A Relation is a logical data structure composed of tuple (row) attribute (column, field) The following data structure are a relation: a table, a materialized view (query) (store data) a query,...
Bigqueyr First Table
BigQuery

is a scanning database, and you’re charged for the amount of data scanned. charges based on the amount of data scanned by your queries. With , you’re not constrained by the storage capacity or...
Data Modeling Chebotko Logical
Cassandra NoSql Database

Cassandra is a NoSql database for transactional workloads that require high scale and maximum availability. Cassandra is suited for transactional workloads at high volume and shouldn’t be considered...
Card Puncher Data Processing
Oracle Database - Materialized view (Snapshot)

in Oracle. Sql Access Advisor (a GUI tool for materialized view and index management) can recommend the creation of materialized views. The end user queries the tables and views in the database....
Data System Architecture
RDBMS - Auxiliary Data Structure (ADS)

Auxiliary data structures are logical replications of data from one or more columns of a table. Auxiliary data structures contain copies of, references to, or data computed from base table data, deleting...
Data System Architecture
Relational Data Modeling - Aggregate Table (Summary Table)

An Aggregate table is a persistence unit of aggregate data. Aggregate tables are also known as summary table (from summary) materialized view A aggregate is a table that contains the result of a query...
Data System Architecture
Relational Data Modeling - View selection problem (recommending the best aggregation tables) - Data Warehousing

The View Selection Problem (VSP) is an NP-Complete problem. Challenges: Design Which materializations to create? Populate Load them with data Maintain Incrementally populate when data changes...
Rewrite
SQL Engine - Query Rewrite

query rewrite is a query performance technique enabled by the query transformer that rewrites the original query to use materialized view instead. As the materialized view (a view stored in a table fashion)...
Event Centric Thinking
Stream - How to capture change of state - Change Data Capture (CDC)

This page is how to capture the changes of state in order to create a stream. The stream processing will then perform an incremental computation Capturing data changes is far from a trivial task, and...



Share this page:
Follow us:
Task Runner