Materialized Views: How to Expedite Your Data Analysis

Stephen Roddewig
Stephen Roddewig

Published:

Executive summaries are a key part of information sharing. They condense the findings of a larger report or whitepaper so that the reader can quickly decide if the document is relevant.

Data engineer building materialized view

Materialized views are very similar to executive summaries as materialized views are derived from data tables, while executive summaries are derived from written documents. Both are intended to reveal more about their origin and provide value to the user.

Let's review the full definition of materialized view.

Get Started with HubSpot's Analytics Software for Free

Materialized view diagram

Image Source

Materialized views can also be generated from tables hosted in separate databases. These are sometimes referred to as snapshots to differentiate them from materialized views constructed from tables in the same database.

This video introduces the concept of a materialized view and also introduces the differences between views and materialized views examined in the next section:

Difference Between View and Materialized View

Let's take a moment to define a view and how it compares to a materialized view. For clarity, a non-materialized view will be referred to as a regular view moving forward.

Regular View

A regular view is generated when a query is run against a data table. This is the fundamental way data engineers and analysts pull data from their databases to inspect it.

Regular View vs. Materialized View

A materialized view fulfills the same function as a regular view: querying data and displaying the results. However, a materialized view stores the retrieved data for future reference. By contrast, a regular view is regenerated each time it is accessed because no data is stored, so the underlying query must be executed.

Materialized views were created to avoid repeatedly executing complicated queries and calculations to display the same results, but they are not necessarily static. Materialized views can be configured to update as data changes in the underlying table, at set intervals, or when manually triggered by the database administrator.

This video reviews the differences between regular views and materialized views and how both are created in SQL:

Materialized View Example

To better understand the concept of materialized views, let's take a look at an example in SQL. In this scenario, you have a table to track product sales that records the customers' names, products sold, and price of each product.

Product sales table

You can create a view of this table with a simple SQL statement (note that this syntax is written for querying Oracle Database).

 

SELECT * FROM PRODUCT_SALES;

However, any time you want to inspect this data, you would need to run this query. This is okay for ad-hoc examinations, but the computational cost would add up over time for data you view regularly. When viewing large datasets or executing complex queries, the costs are magnified further.

Materialized views are an alternative approach to data analysis meant to address this inefficiency. To achieve the same results with a materialized view, you can use the following syntax:

 

CREATE MATERIALIZED VIEW mv_product_sales

BUILD IMMEDIATE

REFRESH FORCE

ON COMMIT

AS

SELECT * FROM PRODUCT_SALES;

Now you can see the same data as the original view, but the base table isn't queried each time you need to access this information. This example is also configured to update each time the data in the base table is changed (ON COMMIT) and to attempt to update only those rows that are affected (REFRESH FORCE). If an incremental update is not possible, the materialized view will fully repopulate.

However, you may not want your materialized view to update each time the base data changes. In this case, you would use ON DEMAND, which allows you to schedule updates or trigger them manually. The documentation has more information about the different options available for building materialized views in Oracle Database.

You have now created a basic implementation of a materialized view. You can run the same queries on a materialized view as you would on a table.

 

SELECT FIRST_NAME FROM MV_PRODUCT_SALES;

You can also perform more advanced SQL operations and store the results in a materialized view, which is ideal for avoiding the cost of repeated complex queries.

 

CREATE MATERIALIZED VIEW mv_product_sales_total

BUILD IMMEDIATE

REFRESH FORCE

ON COMMIT

AS

SELECT SUM(PRICE)

FROM PRODUCT_SALES;

This is especially useful if you expect the data to remain stagnant. For example, if you are generating a final revenue total for the previous quarter's sales.

As you have seen, materialized views support the same SQL queries as regular views. The key difference is the computational cost of retrieving that data versus the timeliness of the data. The next section will review the scenarios when you can use a materialized view and the scenarios when they are not ideal.

When should you use a materialized view?

The data your organization stores and the queries that are valuable to your business operations are unique, so there is no "one size fits all" solution to decide between materialized views and regular views. Below are the major considerations you should weigh before choosing your approach.

Materialized View Drawback

A materialized view's main advantage is removing the need to query the full dataset any time the view is accessed. However, the reduced computing costs can be offset by the memory costs of storing the query results. This is an important distinction to keep in mind as you consider whether a materialized view will optimize resource usage in the next sections.

Using a Materialized View

The best use case for a materialized view is when the following are true:

  • The underlying data does not change often, ideally at all.
  • The query is complex and resource-intensive.
  • You expect to access the query results often.

If one or more of these factors is not true, it would be best to consider using a regular view. A materialized view that needs to be updated often will not reduce computational savings, and if the data is not accessed regularly, then the cost of storing the results in memory may be higher than the cost of submitting a periodic query to the base table.

Using a Regular View

The best use case for a regular view is when the following are true:

  • The base data changes often or you need the most up-to-date information in your view.
  • The query is simple and efficient to execute.
  • You don't expect to access the view regularly.

You'll notice these factors mirror the use case for a materialized view. The overall delineating factor is whether you expect the base data to change often and if you need the most timely data available. A materialized view may still be worth the storage cost if you access the query results more often than the base table changes.

Optimize your analysis with materialized views.

Materialized views are a useful tool to optimize resource usage in your database. Instead of executing queries each time you need data, you can delegate static datasets or complex queries to materialized views to avoid unnecessary delays and computational costs. Maximizing the efficiency of your data processes will allow your entire organization to speed decision-making and take action with better insights.

analytics

Related Articles

Get Started with HubSpot's Analytics Software for Free

GET STARTED FREE