I have created a oracle materialized view which is being used further in multiple queries as source of data.
As MV has huge data (around 2 million ), I created index on one of the columns of MV. This improves performance of queries which are using this MV as source.
But while MV is being full refreshed, I observe that queries which are using MV start showing performance issues again.
Is it because during full refresh, index is dropped and re-created ?
How do i solve this issue ? MV refresh runs for almost 1 and half hours. This means that during this 1.5 hours, queries won't give data in time. I need to keep refreshing MV every 3 hours or less.
Regards,
Besides options mentioned by Paul W, since Oracle 12c there is an option of "Out- of-place" refresh. This is sort of like the "repoint synonym" solution, but managed by Oracle, and no additional synonym.
In Oracle 12c Documentation, the following example was given
DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', method => '?',
atomic_refresh => FALSE, out_of_place => TRUE);
Out-of-place refresh has some restriction. One that made me resort to "repoint synonym" solution was that no constraints are allowed on the MView table.