Solved
Oracle 10 - Force Query rewrite with HINT
Posted on 2011-02-21
Hi all,
Ive recently started doing work with Oracle and Materialized Views. While I understand the logic I am struggling to understand why you would use an Oracle HINT to rewrite a query
/*+ REWRITE (mv1) */
The best example I found was from a paper online which shows the following example
For instance, suppose we had two eligible materialized views:
MONTHLY_SALES_MV, which computes sum of sales by month, and
YEARLY_SALES_MV, which computes the sum of sales by year. If we
wanted to know the sum of sales by year, as shown in the following query, you
would expect query rewrite to pick the latter, since it would read less data.
SELECT t.year, p.product_id, SUM(ps.purchase_price) sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.year, p.product_id;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
|Id |Operation | Name | Rows |Cost |
------------------------------------------------------------------
| 0|SELECT STATEMENT | | 329 | 2|
| 1| MAT_VIEW REWRITE ACCESS FULL| YEARLY_SALES_MV | 329 | 2|
------------------------------------------------------------------
You could, however force query rewrite to use MONTHLY_SALES_
MV with a hint.
SELECT /*+ REWRITE(monthly_sales_mv) */ t.year, p.product_id,
SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.year, p.product_id
Why on earth would you want to do this i.e write a query to take a much longer path ?
Regards
Ben