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: 71
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
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: 71
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
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: 3724
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
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: 71
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.
Previous Topic: Performance Query Tuning help
Next Topic: Need help in tuning this query
Goto Forum:
  


Current Time: Tue Dec 06 12:28:53 CST 2016

Total time taken to generate the page: 0.08286 seconds