Solved

Query rewrite options for materialized view.

Posted on 2010-11-23
4
940 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 500 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_20703
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

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
Where Does Time Value Come From for Database Insert or Update 4 42
plsql job on oracle 18 109
looking for guidance on Oracle Sql Formatting standards 9 45
construct a query sql 11 43
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 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