Oracle Query Rewrite Tips

Oracle Tips by Burleson Consulting

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 = 8.1.5.0.0 (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:

Here are my notes on materialized views query rewrite:

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books