Oracle 10 - Force Query rewrite with HINT

Posted on 2011-02-21
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;
|Id |Operation | Name | Rows |Cost |
| 0|SELECT STATEMENT | | 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 ?


Question by:BPMonk
  • 4

Accepted Solution

MrNed earned 500 total points
ID: 34949115
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.

Expert Comment

ID: 34949123
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.

Author Comment

ID: 34949157
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 ?
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Expert Comment

ID: 34949416
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.
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34953365
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.

Expert Comment

ID: 34956913
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.

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 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