SQL tuning performance optimization with Oracle materialized views

Build it now or built it later

One real feature of relational databases is that complex objects are built from their atomic components at runtime, but this can cause excessive stress if the same things are being done, over-and-over.  You can either do it in-advance, or do it over-and-over.  The problem is inserts and updates.  High update database nay not be able to keep materialized views current and the cost of updating the MV's outweighs the saving from pre-aggregation.

In the world of database architecture, the need to dynamically create complex objects conflicts with the demand for sub-second response time. Oracle's answer to this dilemma is the materialized view. Database designers can use materialized views to pre-join tables, presort solution sets, and pre-summarize complex data warehouse information. Because this work is completed in advance, it gives end users the illusion of instantaneous response time.

Without materialized views you may see unnecessary repeating large-table full-table scans, as summaries are computed, over and over:

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 pre-compute complex aggregates?having Oracle dynamically rewrite SQL to reference pre-computed 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 pre-aggregation

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.

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 pre-created 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. Below 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 Oracle SQL optimizer now has other query rewrite capabilities. It often rewrites correlated subqueries into standard joins. For example, the Oracle 10g 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;

Below we see 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.

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 below.

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.

Conclusions on MV tuning

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.

Materialized View Tuning References

Oracle tuning using materialized views

Oracle materialized view partitioning

Oracle Materialized Views

Materialized Views Tuning

Materialized Views Refresh Performance Tuning

Materialized Views and query rewrite

Oracle materialized views and partitioning

Oracle hidden materialized views parameters

multiple materialized views

Materialized Views in Oracle Warehouse Builder OWB

Oracle materialized views & replication scripts

Guard against performance issues when using hints and views

Oracle DBMS_ADVISOR TUNE_MVIEW

Data structure denormalization with Oracle

Oracle Materialized View performance

Combine Relational And Multidimensional

Oracle 10g New Features training in Oracle10g

Also see: Oracle 10g Materialized Views new features

Note:  In Oracle 10g you can use the dbms_mview package and the dbms_advanced_rewrite package  DECLARE_REWRITE_EQUIVALENCE procedure to improve materialized view automatic query rewrite:

EXEC DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
    '<tag_name>',
    '<original_sql>',
    '<replacement_sql>');