Solved

Oracle 10 - Force Query rewrite with HINT

Posted on 2011-02-21
6
1,371 Views
Last Modified: 2013-11-16
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
0
Comment
Question by:BPMonk
  • 4
6 Comments
 
LVL 7

Accepted Solution

by:
MrNed earned 500 total points
Comment Utility
As with most hints, you use them when the optimizer is not choosing the path that makes the most sense. 99% of the time it will automatically find the MV that is the fastest to use. The rewrite hint is just there to help out in those situations where it needs help.
0
 
LVL 7

Expert Comment

by:MrNed
Comment Utility
Additionally, you can list specific MVs in the hint to tell it to choose from one of them. So you could avoid a particular MV it really wants to use if you felt the need.
0
 

Author Comment

by:BPMonk
Comment Utility
Thanks MrNed but why would you want to miss an MV that gives you a quick response ?  Also when we talk about the SQL statement.  Im a Business Objects BI guy so Im assuming you queries would be through a reporting tool ?  I mean, why would you be using raw SQL to query a DW ?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:MrNed
Comment Utility
Maybe the data in it hasn't been refreshed the way you want the query to work. I think the more common reason would be to hint the optimizer in the right direction when it's gotten mixed up.

There will always be a need to use raw SQL against any database, even if it's just for development and admin/maintenance. Also, some BI tools might need to have embedded hints in the SQL they generate to fix some performance issues.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
hi BPmonk

MrNed has a point here . when you query a database for data the question when using MV is how fresh will you get your data and how fresh do you need it.

if the MV is refreshed periodicaly then sometimes i dont want to use it couse i need uptodate data.
if the MV is constantly refreshed using refresh on commit then i see no reason not to use it.
0
 
LVL 7

Expert Comment

by:MrNed
Comment Utility
Yes ashilo, I only mentioned the refresh point briefly but when I think about it again, that's probably the main reason you would choose one MV over another. I usually only work with refresh on commit MVs.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now