Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle 10 - Force Query rewrite with HINT

Posted on 2011-02-21
6
Medium Priority
?
1,424 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

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!

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

578 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