4.6 Query rewrite

The greatest benefit of using Summary Management in Oracle is automatic query rewrite. Traditionally, when summary tables were used, the queries on the base tables had to be modified to use the summary table instead. However, with query rewrite, the Oracle optimizer automatically determines if the result of a query can be obtained from a materialized view and, if so, transparently rewrites the query in terms of the materialized view. If the optimizer determines that the summary is insufficient to answer the query, it uses the detail data. This means that no application changes are needed in order to create and use summaries. Further, end users do not have to be aware of the existence of summaries and hence summaries can be created and modified without impacting users.

Oracle supports several types of query rewrite transformations, allowing a single summary to be used to answer several queries. We will illustrate several of these in the following sections, including:

• Text match

• Aggregate rollup

• Join-back

• Filtered data

• Rollup using a dimension object

To enable queries to be rewritten, the materialized view must be created with the ENABLE QUERY REWRITE clause. Also, the QUERY_REWRITE_ENABLED initialization parameter must be set to TRUE and the cost-based optimizer must be used.

-- enable query rewriteALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;-- enable cost-based optimizerALTER SESSION SET OPTIMIZER_MODE=" all_rows";

Our first few examples will use the following materialized view, which computes the sum of sales for products by month.

CREATE MATERIALIZED VIEW monthly_sales_mv ENABLE QUERY REWRITE AS...