More often than not, the types of queries that use materialized views are both faster, and use fewer resources than those that are still processing queries directly to base tables. But the key to understanding the potential that materialized views bring with them involves first understanding as much about them as possible.

What is a Materialized View?

In the world of computing, a materialized view is a database object made up of any results that were obtained from a query. Note that this could be a local copy of data that is also stored in a separate remote location, or it could have been derived from a join result. Sometimes, a materialized view could even be created after a user performed an aggregate function, thus allowing it to act as a summary of that database information.

Oracle Database first made materialized views available in version 8i of their software, and it has been a part of every release since. Just a few of the environments that support materialized views include, but are not limited to, others like SQL Server, BigQuery, PostgreSQL and Sybase SQL Anywhere.

View vs. Materialized View

What’s the difference between a regular view versus a materialized view? There are a number of important things you’ll need to keep in mind.

Chief among them, the fact that a view itself is a precise type of virtual table created via the “Create View” command. As the name suggests, it collects all data obtained from any relevant query that you’ve executed. A view will always be compiled every time it is either used or accessed in any way. This is how a view is able to make sure that you’re always getting the most updated data, no matter what – it’s literally designed to do that.

If you make any changes to the data contained inside that view, it will get pushed out to the table in which it originated. Along the same lines, any changes made to that base table are automatically pushed to the view so that the same information is in both places at once.

Because of this extra step, a view will always be a bit slower than a materialized view when it comes to performance. The upside is that this really doesn’t require much in the way of storage space. But it does mean that you’ll be sacrificing a bit in terms of performance.

With a materialized view, you’re talking about a physical copy of the information contained in that original table. This means that it isn’t updated every time you interact with it – you have to go out of your way to update it manually. Or, if you’d prefer, you can have it update using pre-defined triggers.

Because of this, a materialized view is always going to respond much faster than its traditional counterpart. However, if you don’t make an effort to confirm that it has been updated, you also run the risk of retrieving stale data.

Why Use a Materialized View?

So at this point, you’re likely asking yourself, “Why use a materialized view in the first place? What can this do for me that a simple view cannot?” The key thing to understand has to do with the costs that you accrue whenever a database is queried.

Even a simple query still needs to be parsed, validated, planned, optimized and executed. In an operational sense, this equates to CPU time. This means memory usage. This even directly impacts your opportunity costs as time goes on. All of this adds up – especially as the application you’re working with continues to evolve and become more resource-intensive.

Savvy developers are always looking for a way to cut costs, and materialized views are a big part of how they’re able to do it. Remember that the results obtained are always kept in memory – meaning that they are only updated when absolutely needed. This is the opposite of what happens when you query tables or use logical views; those results are updated constantly. Therefore, using materialized views is a great way to significantly reduce your costs without impacting performance in a way that will also affect your end users.

Materialized Views Best Practices

For best results, you should always make sure that your materialized view reflects query patterns against the original table you’re working with. Don’t just create a materialized view for every iteration of a query. Instead, create one to help focus on a broader set of queries.

Likewise, if the base table you’re working with is partitioned, your materialized view may grow to a size larger than you’re comfortable with. Therefore, it should be partitioned, as well, to preserve the performance benefits it is supposed to bring with it.