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

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Query Rewrite Tips

Oracle Database Tips by Donald Burleson

When using materialized views it is critical to properly enable query rewrite so that SQL is automatically transformed to use the materialized views instead of the base tables.

The following parameters are required to enable materialized view query rewrite:

optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced
compatible = (or greater)

However, getting query rewrite working can be tricky, and you often need to create dimensions.  The article the value of meta-data for enabling Query Rewrite, describes the technique of creating dimensions and including the dimension in the materialized view definition to allow query rewrite to function for a variety of related queries:

This article gives a very simple example of such a query rewrite operation and then continues to illustrate how providing some additional meta-data to the database - next to constraints and proper data-type definitions - in the form of Dimensions can make all the different to the Query Rewrite capabilities of the CBO.

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

When is a materialized view used?

Question:  I have noted that sometimes a full match is not required to use a materialized view,  When are the times when materialized view query rewrite are invoked?  When is SQL query rewrite used?

Answer:  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.

  • CHECK constraint - This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of REGION are North, South, East, or West, a CHECK constraint can be added to the table definition to ensure the validity of the region column.

  • NOT NULL constraint - This constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL INSERT and UPDATE time.

  • PRIMARY KEY constraint - This constraint is used to identify the primary key for a table. This operation requires that the primary column(s) is unique, and Oracle will create a unique index on the target primary key.

  • FOREIGN KEY constraint - This is the foreign-key constraint as implemented by Oracle. A foreign key constraint is only applied at SQL INSERT and DELETE times. For example, assume a one-to-many relationship between the CUSTOMER and ORDER tables such that each CUSTOMER may place many ORDERs, yet each ORDER belongs to only one CUSTOMER. The REFERENCES constraint tells Oracle at INSERT time that the value in ORDER.cust_num must match the CUSTOMER.cust_num in the customer row, thereby ensuring that a valid customer exists before the order row is added. At SQL DELETE time, the REFERENCES constraint can be used to ensure that a CUSTOMER is not deleted if rows still exist in the ORDER table.

  • UNIQUE constraint - This constraint is used to ensure that all column values within a table never contain a duplicate entry.

Note the distinction between the UNIQUE and PRIMARY KEY constraint. While both of these constraints create a unique index, a table may only contain one PRIMARY KEY constraint column--but it may have many UNIQUE constraints on other columns.

Here are my notes on materialized views query rewrite:




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.