Jul 1 2008, 03:47 PM
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.
Jul 1 2008, 04:14 PM
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.
Jul 1 2008, 08:10 PM
You also need to set query_rewrite_enabled=true in the init.ora or spfile.
Jul 8 2008, 02:12 PM
I checked query plan, you are absolutely right.
Jul 8 2008, 04:52 PM
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