Using Materialized Views to Improve Oracle Database 12c Performance

In this article, we discuss a feature of the Oracle Database that you can include in your arsenal: materialized views.

Originally called snapshots, materialized views were introduced in Oracle8i and are only available in the Enterprise Edition. Like a regular view, the data in a materialized view results from a query. However, the results of a regular view are transitory—they are lost once the query is complete and, if needed again, the query must be re-executed. In contrast, the results from a materialized view are kept and physically stored in a database object that resembles a table. This feature means that the underlying query only needs to be executed once and then the results are available to all who need them.

Oracle Database 12c allows for synchronous refreshes of the materialized views when configured to use a refresh method besides manual or on-demand. It utilizes partitioning and dependencies between the objects to minimize the time it takes to refresh and maintain the data as close to the underlying tables as possible.

From a database perspective, materialized views are treated like tables:

• You can perform most DML and query commands such as insert, delete, update, and select.
• They can be partitioned.
• They can be compressed.
• 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.

Uses for Materialized Views

Materialized views are used as a performance-enhancing technique. In this section, you learn about the following uses of these views, as they are applicable to the topic of large databases.

• Performing data summarization (for example, sums and 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 operations at once on large numbers of records for the purposes of reporting and analysis. 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 1.

Using materialized views has several advantages over more traditional methods. These include the following:

• Materialized views have a built-in data refresh process, which can provide an automatic update or repopulation of a materialized view without any programming on the part of the DBA.
• As mentioned 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. We expand more on this in the next section when we discuss automatic query rewriting.

Figure 2 illustrates summarization using materialized views.

Query Rewrite

Earlier, you learned that one of the benefits of using materialized views was that they are transparent to the users. But what exactly does that mean and how can they be used if the users can’t see them? In fact, because materialized views are so much like tables, you can give the users access to materialized views, although generally this is not done.

Instead, as indicated in Figure 2, the users always query the tables with the detail data—they don’t usually query the materialized views directly because the query optimizer in Oracle Database 12c knows about the materialized views and their relationships to the detail tables and can rewrite the query on-the-fly to access the materialized views instead. This results in huge performance gains without the user having to do anything special—just query the detail data. There is a maintenance benefit of this feature for the user as well: The queries do not have to change to point to different summary tables, as is the case with the more traditional summarization approach.

In order for the query to be rewritten, the structure of the materialized view must satisfy the criteria of the query. The following two listings demonstrate the query rewrite process. Let’s assume you need to summarize the COMMISSION table you saw in the data compression section using the following query:

 Assume further that a materialized view (called comm_prod_mv) exists that contains summarized commission data by sales_rep_id, prod_id and comm_date (full date). In this case, the query would be automatically rewritten as follows:

By rewriting the query to use the materialized view instead, a large amount of data-crunching has been saved and the results will return much more quickly. Now turn your attention to determining what materialized views should be created.

When to Create Materialized Views

At this point, you may be asking yourself: “How do I determine what materialized views to create and at what level of summarization?” Oracle Database 12c has some utilities to help. These utilities are collectively called the SQL Tuning Advisor and will recommend materialized views based on historical queries, or based on theoretical scenarios. They can be run from the Oracle Enterprise Manager (OEM) Grid Control, or by calling the dbms_advisor package.

Create Materialized Views

Materialized views are created using a create materialized view statement, which is similar to a create table statement. This can be performed using SQL Developer, SQL*Plus, or OEM. The following listing shows a simple example of how to create the comm_prod_mv materialized view mentioned earlier, and Table 1 provides an explanation of the syntax: