Home » RDBMS Server » Performance Tuning » Query Rewrite to Utilize Materialized Views on a Remote Database
Query Rewrite to Utilize Materialized Views on a Remote Database [message #377581] Tue, 23 December 2008 10:42 Go to next message
gunalmel
Messages: 5
Registered: May 2007
Junior Member
We have our materilized views stored at a remote database. Is there a way to utilize query rewrite to rewrite the queries issued on the local database such that queries will refer the materialized views at a remote db?

From the documentation, I already know that the base tables for MVs and MVs should eside in the same instance or the query should be submitted on the instance where MVs reside if base tables are residing in a different remote DB to benefit from query rewrite.

My aim is to utilize query rewrite to reduce the workload on a primary database by passing queries that can be rewritten to use MVs on a remote DB.
Thanks for the guidance,

Report message to a moderator

Re: Query Rewrite to Utilize Materialized Views on a Remote Database [message #377603 is a reply to message #377581] Tue, 23 December 2008 16:58 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
possibly. im not entirely sure.

Have you tried using the DRIVING_SITE hint to change the "instance base" of the query to be based at the remote site?

Report message to a moderator

Re: Query Rewrite to Utilize Materialized Views on a Remote Database [message #377860 is a reply to message #377603] Thu, 25 December 2008 23:36 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It seems implausible. Data for a MV is PULLED across a database link that is defined on the MV database. The master table database does not know where the MV lives, and does not have a DB link to access it even if it DID know.
Ross Leishman

Report message to a moderator

Re: Query Rewrite to Utilize Materialized Views on a Remote Database [message #377946 is a reply to message #377581] Fri, 26 December 2008 18:30 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
If all else fails, you should be able to use the new "advanced query rewrite" to rewrite the query to use the remote database:-

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/qradv.htm#i1010537

http://www.oracle-base.com/articles/10g/dbms_advanced_rewrite.php

[Updated on: Fri, 26 December 2008 18:31]

Report message to a moderator

Previous Topic: Relate question to MViews
Next Topic: Left outer join taking more time
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 28 01:07:03 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.