How Oracle Rewrites Queries

The optimizer uses a number of different methods to rewrite a query. The first step in determining whether query rewrite is possible is to see if the query satisfies the
following prerequisites:

■ Joins present in the materialized view are present in the SQL.
■ There is sufficient data in the materialized view(s) to answer the query.

After that, it must determine how it will rewrite the query. The simplest case occurs
when the result stored in a materialized view exactly matches what is requested by a
query. The optimizer makes this type of determination by comparing the text of the
query with the text of the materialized view definition. This text match method is most straightforward but the number of queries eligible for this type of query rewrite is minimal.

When the text comparison test fails, the optimizer performs a series of generalized
checks based on the joins, selections, grouping, aggregates, and column data fetched.
This is accomplished by individually comparing various clauses (SELECT, FROM,
WHERE, HAVING, or GROUP BY) of a query with those of a materialized view.
This section discusses the optimizer in more detail, as well as the following types of query rewrite:

■ Text Match Rewrite
■ General Query Rewrite Methods

Posted in: Query Rewrite