Oracle Query Rewrite Tips
Oracle Database Tips by Donald Burleson
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
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 = 22.214.171.124.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
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
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
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