Help - Search - Members - Calendar
Full Version: Materialized view name and architecture question
Oracle DBA Forums > Oracle > Oracle Forum
Ninel
Hello,

I need to create a materialized view due to performance issues with one of our regular views.
Say, this regular view is called v_status. I can create a mat view with all the joins v_status has. But I cannot name it v_status, because this object name already exists. And I cannot give it another name, like vm_status, because the application would have to be changed, to make a call to a new view.
I could create vm_status that would mimic v_status, and change v_status just to select from vm_status, but it doesn't look like the right way. But what is?

I'm new to mat views, so please excuse if I'm missing something obvious.

Thanks
michaelasc
Hi
The application will not have to be changed if you called it VM_STATUS.

If enabled, the database will automatically redirect queries that would have gone against V_STATUS to VM_STATUS without your users having to know anything about the existence of the materialized view. This is the beauty and one of the major benefits of a materialized view.

Your users will need to have the GLOBAL QUERY REWRITE database privilege in order for the redirect to work and you will need to refresh the materialized view frequently - daily?. You will also need to analyze the materialized view following each refresh so that the database has up to date statistics to use to determine whether a redirect is or is not needed.

Best wishes
Michael
andrew kerber
You also need to set query_rewrite_enabled=true in the init.ora or spfile.
Ninel
I checked query plan, you are absolutely right.

Thanks
aussie_dba
Ninel,

Also, beware that high updates can slow-down materialized view refreshing.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.