Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query rewrite options for materialized view.

Posted on 2010-11-23
4
Medium Priority
?
950 Views
Last Modified: 2012-05-10
How we can use materialized view for performance by enabling query rewrite?

My doubt is materialized view may not contain recent data, and whenever we write a query that matches m.view, there is no guarantee it will contain recent records.
0
Comment
Question by:sakthikumar
[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 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34197512
If the MV is refreshed on commit it does contain recent records.

The problem here is that your transactions must wait for the MV to refresh before they return

As far as query rewrite, I suggest the online docs or the Internet.  There are a lot of papers out there on it.
0
 
LVL 5

Expert Comment

by:manzoor_dba
ID: 34197990
Hi,

Hope the below will help..

http://smahamed.blogspot.com/2010/11/materialized-views.html

Thanks..
0
 
LVL 19

Accepted Solution

by:
Thommy earned 2000 total points
ID: 34205738
Please see ORACLE documentation for Query Rewrite...

Basic Query Rewrite
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/qrbasic.htm

Advanced Query Rewrite
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/qradv.htm

0
 
LVL 5

Expert Comment

by:Anand
ID: 34273572
Using the refresh on commit option in the MV could cause performance overhead. Avoid it and refresh the MV manually before you query the MV for reporting purposes. This approach is suitable in weekly/monthly reporting purposes using the MV and gives guarantee to you with latest data in MV.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

705 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