Maven4Champ
Technical User
- Jun 16, 2004
- 154
We are currently running an Oracle 10g database environment where we use standard views for reporting. Due to the demand for more efficient production of reports and faster run-times for our end-users, I proposed a solution of using materialized views to be refreshed in off-peak hours. This was well received when I explained the benefits of the MVs as I used them in a prior position and worked great.
In this situation, we have some views that are calling other views, and some complex sub-query/sub-select views that can't be purely materialized without more modification. My alternate to that was to simply select from the original view when creating a materialized view.
We have nearly 100 views we use for reporting and I have built a procedure that does a DBMS_MVIEW.REFRESH of all the MVs and then does a 'C' Commit at the end. I was running this in a DBMS_SCHEDULER job to run in off-peak hours. Here is where it gets interesting.
When creating the MVs, the total execution time from start to finish of building the views from scratch was under 5 hours. When scheduling the job, it ran for nearly 8 hours and caused some problems while running. The DBAs extended the tablespace from 2GB to 8GB which is plenty of room for growth.
My question is - what reccomendations can you make on how I could either A.) Re-write the procedure to speed up the process of refreshing the MVs or B.) Code into the MVs different syntax such as FAST REFRESH in order to improve performance.
My issue is accuracy versus speed. Performing a FAST REFRESH ON DEMAND will simply give an incremental refresh, only renewing the data that wasn't there before. However I wasn't sure if that is an accurate way to perform the duties considering I have Mviews that call other views (not base tables) and also Mviews that call other Mviews. I code for that in my procedure by listing them in the proper order.
Can anyone suggestion some resolutions?
In this situation, we have some views that are calling other views, and some complex sub-query/sub-select views that can't be purely materialized without more modification. My alternate to that was to simply select from the original view when creating a materialized view.
We have nearly 100 views we use for reporting and I have built a procedure that does a DBMS_MVIEW.REFRESH of all the MVs and then does a 'C' Commit at the end. I was running this in a DBMS_SCHEDULER job to run in off-peak hours. Here is where it gets interesting.
When creating the MVs, the total execution time from start to finish of building the views from scratch was under 5 hours. When scheduling the job, it ran for nearly 8 hours and caused some problems while running. The DBAs extended the tablespace from 2GB to 8GB which is plenty of room for growth.
My question is - what reccomendations can you make on how I could either A.) Re-write the procedure to speed up the process of refreshing the MVs or B.) Code into the MVs different syntax such as FAST REFRESH in order to improve performance.
My issue is accuracy versus speed. Performing a FAST REFRESH ON DEMAND will simply give an incremental refresh, only renewing the data that wasn't there before. However I wasn't sure if that is an accurate way to perform the duties considering I have Mviews that call other views (not base tables) and also Mviews that call other Mviews. I code for that in my procedure by listing them in the proper order.
Can anyone suggestion some resolutions?