Dynamically create complex objects with Oracle materialized views

In the world of database architecture, the need to dynamically create complex objects conflicts with the demand for subsecond response time. Oracle's answer to this dilemma is the materialized view. Database designers can use materialized views to prejoin tables, presort solution sets, and presummarize complex data warehouse information. Because this work is completed in advance, it gives end users the illusion of instantaneous response time. Materialized views are especially useful for Oracle data warehouses, where cross-tabulations often take hours to perform. This article explores the internals of materialized views and demonstrates how to precompute complex aggregates—having Oracle dynamically rewrite SQL to reference precomputed aggregate information. This is the first of two articles concentrating on Oracle materialized views.

Materialized views and snapshots
Materialized views are an introduction of redundancy, along the same lines as Oracle snapshots. When an Oracle materialized view is created, Oracle treats the materialized view just as it would an Oracle snapshot. Oracle requires you to specify a schedule for periodic updates. Updates are accomplished by way of a refresh interval, which can range from instantaneous rebuilding of the materialized view to a hot refresh that occurs weekly.

Manual preaggregation
Prior to Oracle8, database administrators using summaries spent a significant amount of time manually identifying which ones to create and then creating, indexing, and updating them, and advising their users which ones to use. Figure A illustrates the manual process.

Figure A

Manually creating summary tables to speed SQL queries

The problem with manually creating summary tables is that you have to tell the end user to go to the new table. There was no Oracle mechanism to automatically rewrite the SQL to go to the precreated summary. Materialized views provide an alternate approach.

Materialized views are very popular in Oracle systems where performance is critical and complex SQL queries exist against large tables. Generally, we see materialized views used in two areas: aggregation and replication.

In terms of aggregation, materialized views improve query speed by rewriting a query against the base table with a query against the preaggregated summary table via the following:

Precalculated summaries—The sum, avg, min, max, count(*), count(distinct x) functions are utilized.

Prejoined tables—Tables are prejoined to improve performance.

Materialized views can also be used to replicate data, which was formerly achieved using the create snapshot statement. In Listing 1, we see that the Oracle create snapshot syntax gets a reply from Oracle stating “Materialized View Created.”

Automatic SQL query rewrite
The query optimizer automatically recognizes when an existing materialized view can be used to satisfy a request. Next, it transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables, resulting in a significant performance gain.

The Oracle9i SQL optimizer now has other query rewrite capabilities. It often rewrites correlated subqueries into standard joins. For example, the Oracle9i SQL optimizer automatically detects situations in which someone uses a not exists clause with an uncorrelated subquery and replaces the SQL with an equivalent query that runs much faster using a standard-order outer join with a not null criterion.

Here is the SQL query before rewrite:

select customer_name from customer
where not exists (select customer_name from bad_credit);

Here is the same query after automatic query rewrite:

Select customer_name
from customer c, bad_credit b
where b.customer_name(+) = c.customer_name
and b.customer_name is null;

Figure B shows how the Oracle SQL optimizer checks the Oracle data dictionary for the presence of a materialized view whenever a new SQL statement enters the Oracle library cache.

Figure B

Oracle SQL query rewrite mechanism

In the next example, we create a materialized view that determines the average salary for each job in the database. Once the materialized view is created, we can run queries against the base table and use an Oracle hint to direct the SQL optimizer to fetch the average salary from the materialized view rather than performing an expensive and time-consuming scan against the emp table as shown in Listing 2.

When is SQL query rewrite used?
Oracle is very sophisticated in SQL query rewrite capability. The Oracle DBA can control the propensity of the SQL optimizer to go to the materialized views to service the query. The options are as follows:

Full SQL text match—In this method, the SQL text of the query's select statement clause is compared to the SQL text of the select clause in the materialized view’s defining query.

Partial text match—If a full SQL test match fails, the optimizer will attempt a partial SQL text match. The optimizer compares the remaining SQL text of the query (beginning with the from clause) to the remaining SQL text of the materialized view’s defining query.

No match—If the full and partial SQL text matches both fail, the optimizer uses general query rewrite methods that enable the use of a materialized view even if it contains only part of the data, more than the data, or data that can be converted.

Tips for using materialized views
When using query rewrite, you create materialized views satisfying the largest number of SQL queries. For example, if you identify 20 queries commonly applied to the detail or fact tables, you may be able to satisfy them with five or six well-written materialized views.

If you are unsure which materialized views to create, Oracle provides a set of advisory functions in the DBMS_OLAP package to help in designing and evaluating materialized views for query rewrite.

If a materialized view is to be used by query rewrite, it must be stored in the same database as its fact or detail tables.

A materialized view can be partitioned, and you can define a materialized view on a partitioned table and one or more indexes on the materialized view.

Conclusion
Oracle's introduction of materialized views significantly improves the performance of Oracle systems required to process complex SQL statements while delivering subsecond response time. In the next installment, we’ll look at the internals of materialized views and SQL query rewrite and gain insight into how to implement this powerful performance feature.