Database Reference
In-Depth Information
Notice that the number of logical reads, compared to the original query, has dropped from 3,094 to 3. Also notice
that the
MAT_VIEW ACCESS FULL
access path clearly states that a materialized view is accessed. This access path
operates as a
TABLE ACCESS FULL
. It's merely a naming convention used to conveniently point out that a materialized
view is being used. For all practical purposes, the two access paths are absolutely the same.
Directly referencing the container table is always an option. But, if you want to improve the performance of an
application without modifying the SQL statements it executes, there's a second powerful possibility: use
query rewrite
.
■
Note
Query rewrite is available only in enterprise edition.
The concept of query rewrite is straightforward. When the query optimizer receives a query to be optimized,
it can decide to use it as is (in other words, to
not
use query rewrite), or it can choose to rewrite it so as to use a
materialized view that contains all, or part of, the data required to execute the query. Figure
15-2
illustrates this. The
decision, of course, is based on the cost estimated by the query optimizer for the execution plans, with and without
query rewrite. The execution plan with the lower cost is used to execute the query. The
rewrite
and
no_rewrite
hints
are available to control the query optimizer's decisions.
Figure 15-2.
The query optimizer can use query rewrite to automatically use a materialized view
