Informatica Tutorials

Big Data Analytics

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:

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!

Related Posts Plugin for WordPress, Blogger...

Please Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More

Follow TutorialBlogs
Share on Facebook
Tweet this Blog
Add Blog to Technorati