Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

SQL tuning performance optimization with Oracle materialized views

Oracle Tips by Burleson Consulting

April 2, 2002 - Updated August 2012


2012 Update - Materialized views are one of the single most important SQL tuning tools and they are a true silver bullet, allowing you to pre-join complex views and pre-compute summaries for super-fast response time.  I've devoted over a hundred pages to SQL tuning with materialized views in my book "Oracle Tuning: The Definitive Reference", and also see "Oracle Replication" a deeply-technical book on creating and managing materialized views.

Also see my notes on Finding Oracle materialized view opportunities.

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

create snapshot
  cust_snap
on
  customer
refresh fast
  start with sysdate
  next sysdate + 1/1440
as
select * from customer@remote;
 
  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.

create materialized view
   job_avg_sal
enable query rewrite
as select job, avg(sal)avg_sal
   from emp
   group by job;
select /*+ rewrite(job_avg_sal) */
   avg(sal)
from emp where job = ?CLERK?;


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

 For free resources, check my related notes on materialized views:

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>');
 

From Mark Rittman:  Oracle 9i came with two packages, DBMS_MVIEW.EXPLAIN_MVIEW and DBMS_MVIEW.EXPLAIN_REWRITE that could be used to diagnose why a materialized view wasn't being used for query rewrite. However, although these packages told you why rewrite hadn't happened, they left it down to you to work out how to alter your CREATE MATERIALIZED VIEW statement to ensure that rewrite happened correctly. Oracle Database 10g comes with a new advisor package, DBMS_ADVISOR.TUNE_MVIEW, that takes as its input a CREATE MATERIALIZED VIEW DML statement, and outputs a corrected version that supports query rewrite and features such as fast refresh.
 
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.