MView refresh issue
We changed one of our views to a Materialized View in order to improve performance of the queries that hit the view. However, since making this decision we have run into a number of issues with maintaining the data in the MV.
There have been access issues in the lower environments, the production environment was refreshing the view on every Read, and now the only way to refresh the view in prod is to delete and recreate the MV causing every function or procedure that uses it to become invalid in the process.
The big issue is surrounding the refresh method for the MV. The data in the tables that the view reads are updated only during releases and primarily when we are implementing new clients. They are not updated by users through the application. When they are updated during a release, we will have user validation work that is performed shortly after the update so we need the updates to be visible by the application shortly thereafter.
0