Database Reference
In-Depth Information
To take advantage of query rewrite, it must be enabled at two levels. First, you have to set the
query_rewrite_
enabled
initialization parameter to
TRUE
. Second, you have to enable it for the materialized view. The following SQL
statement shows how to enable query rewrite for a materialized view that already exists:
ALTER MATERIALIZED VIEW sales_mv ENABLE QUERY REWRITE
Once query rewrite is enabled, if you submit the original query, the query optimizer considers the materialized
view as a candidate for query rewrite. In this case, the query optimizer does, in fact, rewrite the query to use the
materialized view. Notice that the
MAT_VIEW REWRITE ACCESS FULL
access path clearly states that query rewrite
takes place:
SELECT p.prod_category, c.country_id,
sum(quantity_sold) AS quantity_sold,
sum(amount_sold) AS amount_sold
FROM sales s, customers c, products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 81 | 3 |
| 1 | SORT ORDER BY | | 1 | 81 | 81 | 3 |
| 2 | MAT_VIEW
REWRITE
ACCESS FULL| SALES_MV | 1 | 81 | 81 | 3 |
---------------------------------------------------------------------------------------
In summary, with query rewrite, the query optimizer can automatically use a materialized view that contains the
data required to execute a query. As an analogy, it's similar to what happens when you add an index to a table. You
(usually) don't have to modify the SQL statements to take advantage of it. Thanks to the data dictionary, the query
optimizer knows that such an index exists, and if it's useful for executing a SQL statement more efficiently, the query
optimizer will use it. The same goes for materialized views.
When the base tables are modified through DML or DDL statements, the materialized view (actually, the
container table) may contain
stale
data (“stale” means “old”—that is, data that's no longer equal to the result set of
the materialized view query, if executed on the new content of the base tables now). As a result, the database engine
stops using the materialized view for query rewrite. For this reason, as shown in Figure
15-3
, after modifying the
base tables, a
refresh
of the materialized view has to be performed. You can choose how and when the refresh of a
materialized view is performed.
