Home » RDBMS Server » Performance Tuning » Query Rewrite in Materialised Views (merged)
Query Rewrite in Materialised Views (merged) [message #354961] Wed, 22 October 2008 01:37 Go to next message
hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI
Member
Hello All

Here is my Query

create materialized view ROLE PARALLEL 4 BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS select b.rolename , count(distinct a.roleuser) AS PORTAL ,count(distinct b.roleuser) AS HR from psroleuser@hrtoep a, psroleuser b where a.rolename = b.rolename and b.DYNAMIC_SW='Y' group by b.rolename;

Can anyone let me know that is it becuase of the DB link the query is failing to rewrite.IF MV has a DB link is it that a query will not rewrite

Regards
mm

Report message to a moderator

Query Rewrite in Materialised Views [message #354963 is a reply to message #354961] Wed, 22 October 2008 01:38 Go to previous messageGo to next message
hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI
Member
Hello All

Here is my Query

create materialized view ROLE PARALLEL 4 BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS select b.rolename , count(distinct a.roleuser) AS PORTAL ,count(distinct b.roleuser) AS HR from psroleuser@hrtoep a, psroleuser b where a.rolename = b.rolename and b.DYNAMIC_SW='Y' group by b.rolename;

Can anyone let me know that is it becuase of the DB link the query is failing to rewrite.IF MV has a DB link is it that a query will not rewrite

Regards
mm

Report message to a moderator

Re: Query Rewrite in Materialised Views [message #354991 is a reply to message #354963] Wed, 22 October 2008 02:36 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do a search in the manual for DBMS_MVIEW.EXPLAIN_MVIEW.

When you run it, it will give you the reason why ANY MV will not re-write.
Ross Leishman

Report message to a moderator

Re: Query Rewrite in Materialised Views [message #354997 is a reply to message #354991] Wed, 22 October 2008 03:00 Go to previous message
hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI
Member
Yes have done that and found out that DB Link is not allowing query ot rewrite,So does that mean we cannot use dblink while creating a MV.

Report message to a moderator

Previous Topic: Performance Query Tuning help
Next Topic: Need help in tuning this query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 26 02:52:32 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.