Solved

Query rewrite options for materialized view.

Posted on 2010-11-23
4
936 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can i Import Access Table Into Oracle Using Toad 36 276
Distinct values from all columns in a table?? PL SQL 4 58
format dd/mm/yyyy parameter 16 48
date show only hh:mm 2 38
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

749 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