Index on Oracle Materizalised does not seem to be working while MV is being refreshed

Question

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,

Answer 1

This is not due to the index (which is not dropped, but is being modified in-place during the refresh), but rather to something called "read-consistency". You are using a normal, atomic refresh which does a delete of all rows and then re-inserts the new rows in their place all in a single transaction. This permits users to continue to see the old version of the table the entire time until the refresh is complete. But all those changes - the inserts and the deletes, both in the table blocks and the index blocks, all have to be rolled back to create a CR (consistent read) version of each block so that your user session sees what used to be there before the refresh started. This requires a lot of reading from rollback segments and can really tank query performance severely.

Your other easy option is to make the refresh non-atomic, which will make the refresh much faster (does a truncate rather than delete, and reloads with direct path instead of conventional inserts), but the table will be empty so effectively creating a down period for the table. This may not be acceptable either.

There are two common solutions to this if neither of the above are acceptable:

1. Create two MVs and a synonym. Repoint the synonym to one while refreshing the other, then when done point to the newly refreshed table. Users refer only to the synonym so are directed to which ever MV was most recently completed. The downside is it causes cursor and code invalidations every time the synonym flips. But it works.

2. Don't use MVs at all - write a simple procedure that loads your query results into a GTT (global temporary table, which is in temp space) and then does two surgical MERGE statements from this GTT against your "MV" table (not really an MV, just a normal table) that target only rows needing work. The first merge inserts new rows and updates changed rows. The second merge deletes missing rows. (Be sure to find your changed rows inside the USING block by pre-joining inside it to the target - do not use the WHERE clause of the WHEN MATCHED THEN UPDATE clause. You should also hint for parallel query in the USING block, but don't enable "parallel DML" so you don't need to commit between the two merges and present to others an incomplete stage of change before the deletes are done).

   This is far superior to any of the real MV options because it surgically changes only what needs to be changed, without recreating all the same data over and over again for no reason. Other reading sessions would use consistent read to see the old version of the data while this is happening, but since (a) the expensive query portion is completed before any changes are made and (b) the vast majority of the table won't be touched at all, that rollback work to do the CR reads would be short-lived and minimal and not noticeably impact performance. It also doesn't involve any dictionary changes (DDL) and so no cursor/code invalidations like the synonym option does. This has become my primary choice for important "MVs" that need performant queries at all times.

Answer 2

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.