Discovering SQL: A Hands-On Guide for Beginners

VIEW REVISITED

In the previous chapter, we defined a VIEW as a frozen query, emphasizing that unlike a table it stores no data. Here we introduce views that do just the opposite: materialized views.

The idea behind a materialized view is to combine benefits of a VIEW and a TABLE into a single database object, usually for performance reasons. An SQL view offers ultimate flexibility; the data can be pulled in from several tables, aggregated, ordered, and otherwise made user-friendly. Yet, there is a price to pay; even with advanced RDBMS optimization, a query might be executed once for each request, and you cannot index dynamic data. With the table, on the other hand, you have all the advantages of index optimization, but to use data from more than one table, you have to run an SQL query.

A materialized view offers you the best of both worlds. As a query it collects the data from different tables, and as a table it persists it in the database. The catch? You sacrifice concurrency. Materialized views have to be updated periodically. The usage scenarios for materialized views are those when large amounts of relatively stable data need to be queried by a large number of users, and persisting the query would offer an increased performance; the data can be refreshed during off-peak hours, along with rebuilding the indices. You get fast and simple SELECT, even though the data are as up to date as the last refresh.

Materialized views were introduced by Oracle, and adopted by a number of ...