Materialized View. In data warehousing, a materialized view is a pre-built summary of some dimension that can be queried and used to answer business questions. It’t not populated directly from the source tables but by using a view that has been created earlier, which is called as the materialized view.
 |
| A Materialized View |
Materialized View
This article will focus on what exactly is a materialized view and the benefits it can offer to your data warehouse. If you are someone who works with data warehouse on daily basis, this blog might interest you. Let’s get started!
What is a Materialized View?
A materialized view is a pre-built summary of some dimension that can be queried and used to answer business questions. Now, the concept of summaries is nothing new to data warehousing. But a materialized view takes this concept to the next level by doing two things.
 |
| What is a Materialized View? |
First, a materialized view caches the results of a query so that it can be reused again and again, without having to re-execute the underlying query. This can save a lot of time in data warehousing environments where the same questions come up over and over again.
Second, a materialized view can be refreshed periodically to keep it up-to-date with the latest version of the underlying query. So, if the underlying data in a table changes, the materialized view can be refreshed to reflect those changes.
What Does A Materialized View Offer?
A materialized view offers the speed and scalability of an in-memory summary, with the flexibility and user control of a select statement. This is made possible because a materialized view is a pre-built summary, which is created by pre-aggregating the data based on certain conditions. Now, the pre-aggregation of data can happen at either of these levels
- the aggregation level or the summarization level. While the former is a process of creating a smaller subset of data from the source table, the latter is about creating a summary of data, which is a subset of a table.
Create Materialized Views - Steps for DBA
There are a few steps that you need to follow, in order to create a materialized view. They are:
1- Choose the source table for the view - You need to choose the table from which the view is created. It should be a table that gets referenced in the business queries regularly.
2- Choose the condition for summarizing data - The pre-aggregation will happen based on some conditions in the source table. So, you need to decide the conditions under which you are creating the summarization.
3- Define the destination view - Once you have decided the source table and the conditions, you need to create a view, so that the summarization is saved in that view.
4- Refresh the view periodically - You can review the view and refresh it as and when required.
5- Drop the view when it is not required - You can drop the view when it is no longer required or if you want to create another materialized view.
Limitations of Materialized Views
Apart from the fact that a materialized view is read-only, there are four limitations associated with it. Let’s discuss them one by one.
1- The first one is that it is read-only. This is because a materialized view is a summary of data, which is created once and then used regularly to answer business questions.
2- The second limitation is that it is not updatable. This is because once the data is aggregated and saved in the view, it can’t be changed. The only way for the data to be updated is to re-create the view.
3- The third thing is that it is not distributed. This is because the materialized view is stored in the data warehouse and not in the data source.
4- The last limitation is that it doesn’t have a transaction associated with it. This is because the data in the view is updated once and then used for the rest of the business.
A Materialized View Is A Pre-Built Summary
A materialized view is a pre-built summary of some dimension that can be queried and used to answer business questions. It’t not populated directly from the source tables but by using a view that has been created earlier, which is called as the materialized view.
This article will focus on what exactly is a materialized view and the benefits it can offer to your data warehouse. If you are someone who works with data warehouse on daily basis, this blog might interest you. Let’s get started!