Databases Reference
In-Depth Information
In this case we can see that, even when we have submitted the same query to the database,
our query was rewritten to use the materialized view
MV_SALES_BY_PRODUCT
. This behavior
is due to the
ENABLE
QUERY
REWRITE
clause in the
CREATE
MATERIALIZED
VIEW
statement executed in step 2.
The result is astonishing: the statistics tell us that there are only 8 consistent gets to
achieve the same result. The absence of physical read is due to the fact that the rows of
the materialized view are already in the buffer cache from the previous statements.
However, from the execution plan, the number of rows processed is 72, and each row
is 648 bytes long. The following is the result of the query after flushing buffer cache:
In the latter case, we have 4047 consistent gets and 240 physical reads: there is a
difference of two orders of magnitude in the physical reads and an order of magnitude
in the consistent gets, resulting in a great performance improvement.
The developer may not know that a materialized view is active in the database; this behavior
is an interesting aspect of the materialized views. It is easy to identify queries that can benefit
from the introduction of a materialized view. The queries will benefit automatically, with the
new database object, without changing anything in the original query.
The magic behind this feature is the query rewrite mechanism. When a query is submitted,
the parser tries to identify if there is any materialized view that could answer the query,
reducing physical reads and response time. If such a materialized view is present, the query
is automatically rewritten using the materialized view instead of the base table(s), originally
involved. Later, if we drop the materialized view, the query is still functional, although slower
than before.
