Solved

Oracle 10 - Force Query rewrite with HINT

Posted on 2011-02-21
6
1,401 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 7

Accepted Solution

by:
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.
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

739 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