| Team Fly | |
They can be parallelized.
You can create indexes on them.
Materialized views are different in other ways and have some interesting features associated with them. Before we talk about those, let's look at some ways to use materialized views.
Materialized views are used as a performance enhancing technique. Following are some usage examples. In this section, we will be discussing the first three uses, as they are applicable to our topic of large databases.
Performing data summarization (for example, sums, averages)
Prejoining tables
Performing CPU-intensive calculations
Replicating and distributing data
In large databases, particularly data warehousing environments, there is always a need to summarize, join, perform calculations, or do all three at once, on large numbers of records for reporting and analysis purposes. To improve performance in the past, a combination of views and physical tables were usually implemented that contained the results of these operations. The summary tables would require some type of extraction, transformation, and load (ETL) process to populate and refresh them. In addition to the base tables containing the detailed data, the users would need to know which combinations of the views and/or summary tables to use. These structures are illustrated in Figure 9-7.
Using materialized views has several advantages over more traditional methods. These include the following:
Materialized views have a built-in data refresh process, which eliminates the need for custom ETL.
As we said earlier, the data in materialized views can be partitioned, using the same techniques that apply to tables.
Materialized views are transparent to the users. This is probably the most attractive feature of using materialized views, and we will expand more on this in the next section when we discuss automatic query rewriting.
Figure 9-8 illustrates summarization using materialized views.
| Team Fly | |