Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle 10 - Force Query rewrite with HINT

Posted on 2011-02-21
6
Medium Priority
?
1,419 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 2000 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.
0
 
LVL 7

Expert Comment

by:MrNed
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.
0
 

Author Comment

by:BPMonk
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 ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:MrNed
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.
0
 
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.
0
 
LVL 7

Expert Comment

by:MrNed
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
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

971 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