Example of Query Rewrite

Consider the following materialized view, cal_month_sales_mv, which provides
an aggregation of the dollar amount sold in every month:

CREATE MATERIALIZED VIEW cal_month_sales_mv
ENABLE QUERY REWRUTE AS
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_mont_desc;

Let us say that, in a typical month, the number of sales in the store is around one
million. So this materialized aggregate view will have the precomputed aggregates for
the dollar amount sold for each month. Now consider the following query, which asks
for the sum of the amount sold at the store for each calendar month:

SELECT t.calendar_month_desc, SUM(s.amount_sold)
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

In the absence of the previous materialized view and query rewrite feature, Oracle will have to access the sales table directly and compute the sum of the amount sold to return the results. This involves reading many million rows from the sales table
which will invariably increase the query response time due to the disk access. The join in the query will also further slow down the query response as the join needs to be computed on many million rows. In the presence of the materialized view cal_
month_sales_mv, query rewrite will transparently rewrite the previous query into
the following query:

SELECT calendar_month, dollars
FROM cal_month_sales_mv;

Because there are only a few dozens rows in the materialized view cal_month_
sales_mv and no joins, Oracle will return the results instantly. This simple example
illustrates the power of query rewrite with materialized views!

Posted in: Query Rewrite