Brittney Parker has always been interested in software and databases and expanding her education of all things technical. She has taken numerous coding bootcamps and development workshops to further her education in the tech space. Brittney is a writer for Girls Write Tech, which specializes in technical writing content and aims to encourage more female developers to share their knowledge.
In the world of computing, a database is an organized collection of data that is typically both stored and accessed electronically from within a computer system.
A database management system, otherwise known as a DBMS, is a piece of software that allows end-users (along with various applications) to interact with the database itself to both collect and analyze the information contained inside.
All of this is important to understand within the context of materialized views, which play an important role in how users interact with and extract value from databases in a variety of situations. Today, we will discuss materialized views in detail with examples.
This tutorial at a glance:
- What is a Materialized View?
- Why use Materialized Views?
- How to create a Materialized View
- Materialized View vs View
- Materialized View vs Table
- What to learn next
What is a Materialized View?
A materialized view is a particular type of database object that contains any results derived from a query. Think of this like a replica of a target master from a particular moment in time. Materialized views are precomputed. They will periodically cache query results to improve a database’s performance.
Depending on the situation, it could be a local copy of data that is stored somewhere remotely, or it could be the product of a join result, or it could even be the summary of said data that was created using an aggregate function.
Materialized views were first implemented by the Oracle Database and have been available in every version from 8i on. Additional environments that support materialized views include PostgreSQL, SQL Server, Sybase SQL Anywhere, BigQuery, and more.
Materialized View Examples
For example, let's say you have a database with two tables: one contains the number of employees in your business, and the other contains the number of departments in your business.
Using a materialized view, you could query the database to retrieve all the employees who are associated with a particular department.
Or, say you have a database with two tables: one for the total number of sales you've made and one for the total amount of revenue you're generating. You could use a materialized view to see how much revenue each sale brings with it in real time.
Why Use Materialized Views
Every single time you query a database, you always accrue some type of cost. Even something as seemingly straightforward as a query still means that you are parsing, validating, planning, optimizing, and executing that query, which equates to CPU time, memory usage, opportunity costs, and more.
As your application continues to grow and become more demanding, naturally, you're looking for opportunities to reduce those costs as much as possible.
This is where materialized views come into play.
Because the results of a materialized view are maintained in memory, they are only updated when it is expressly necessary to do so. Because of that, they can significantly reduce your overall costs as opposed to querying tables or using logical views.
Pros of Materialized Views
- Improve performance by precomputing expensive operations
- Increase the speed of queries on very large databases
- Efficiently execute expensive queries or expensive parts of your queries
Cons of Materialized Views
- Not every database type supports materialized views
- Materialized views are read-only
- You cannot create keys, constraints, triggers, or articles
For more on why you should use a materialized view, check out this article from Materialize.
How to create a Materialized View
To create a materialized view in the tool you're working with, you can use a DML statement to create a basic table, load data into it, and create a materialized view as a result.
An example of an SQL command that could be used to create a materialized view is as follows:
CREATE [ OR REPLACE ] [ SECURE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <name>
[ COPY GRANTS ]
( <column_list> )
[ COMMENT = '<string_literal>' ]
[ CLUSTER BY ( <expr1> [, <expr2> ... ] ) ]
AS <select_statement>
An example of a command in Oracle that could be used to create a materialized view is as follows:
CREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT * FROM <table_name>;
An example of a command for PostgreSQL that could be used to create a materialized view is as follows:
CREATE MATERIALIZED VIEW MV_MY_VIEW
[ WITH (storage_parameter [= value] [, ... ]) ]
[ TABLESPACE tablespace_name ]
AS SELECT * FROM <table_name>;
Version 9.3 and newer natively support materialized views.
Materialized View vs. View
So, what are the major differences between a view and materialized view, and why should you use one over the other?
The view is a very particular type of virtual table that is created using the command "Create View." It contains all data obtained from a relevant query expression.
If you're working with one or even multiple base tables or views, you can use this to create a View. You can also query your View in the same way you would use one of those base tables.
The key thing to understand is that a View is always computed every time it is used or accessed in any way. Because of this, you're always getting the most updated data in any View that you query.
If you make any type of update to the content in a View, it will always be "pushed back" and updated in the original table. Likewise, the reverse is also true: any changes that are made to the original base table are instantly reflected in the View.
What this means, however, is that the performance of a View will always be slower than that of a materialized view. The major advantage is that a View doesn't actually require storage space. You can also have total control over which users can or cannot view sensitive information within the database itself.
A materialized view, on the other hand, is a physical copy of those original base tables. Think of it more like a photograph of the original base table. The key difference is that a materialized view will not be updated every time it is interacted with.
If you don't go through the trouble of updating it manually, you can do so with the help of certain pre-defined triggers.
The upshot is that a materialized view will always respond faster to a query than a traditional View would, but you could also be retrieving stale data if you're not careful.
Materialized View vs. Table
The answer to that question within the context of environments like Oracle or MySQL ultimately comes down to a concept called "dynamic query rewriting." It's key to understand that a materialized view won't properly define the relationships between data and the original base table. It will also allow users to pre-compute expensive joins and aggregations as well.
Likewise, the materialized view will also have certain options that can be used to automatically refresh the data being queried. Not only will this ultimately improve the ease of maintenance, but it will also go a long way towards improving efficiency as well because those changes can be tracked by row.
To put it another way, the materialized view will always be in sync with the "real data" in the table. You don't actually have to go out of your way to do anything because, at that point, the database will do it for you.
What to learn next
Materialized views are a great way to reduce costs during the development of your application, especially as it continues to grow. Not only will you save wall clock time, but you'll increase efficiency and save things like CPU time and memory usage too, thus translating into actual monetary savings before you know it.
But there is more to learn. To continue your learning, next you should check out these concepts:
- Creating materialized views with Big Query
- Joining a materialized view
- Suspending updates
- Using shared data with materialized views
To get some hands-on practice with databases, check out Educative's course Database Design Fundamentals for Software Engineers. In this course, you'll learn about the fundamental concepts of databases. You will also be exposed to techniques like normalization that help to increase the efficiency of databases.
Happy learning!
Continue reading about databases on Educative
- What are SQL Joins? Types of SQL joins explained
- What are database schemas? 5-minute guide with examples
- 7 Tips for Query Optimization in SQL Servers
Start a discussion
Why do you like using materialized view? Was this article helpful? Let us know in the comments below!
Latest comments (0)