Oracle - Materialized View still accessible during complete refresh. How does this work?

oracle oracle10g materialized-views
contactmatt picture contactmatt · Aug 9, 2011 · Viewed 8k times · Source

In one of our applications, we have a massive Materialized View that refreshes three times a day, and takes seven hours to refresh. (Not ideal, I know). This perplexed me, because I surely thought that users and sessions could not access this materialized view while it was being refreshed, but apparently they can!. (The type of refresh is a complete refresh)

During a complete refresh, to my understanding, the existing dataset is dropped and the query is then re-executed. If this is true, then how are users/other sessions able to access the materialized view while the materialized view is being refreshed?

Answer

Justin Cave picture Justin Cave · Aug 9, 2011

There are two different ways that a complete refresh can happen-- an atomic refresh or a non-atomic refresh. An atomic refresh simply issues a DELETE to delete all the rows in the materialized view and then does an INSERT to insert the new data. This is all within a single transaction so Oracle's standard multi-version read consistency architecture lets Oracle show other sessions the old data until the refresh completes. In a non-atomic refresh, Oracle does a TRUNCATE on the materialized view and then a direct-path INSERT to insert the new data. This is substantially more efficient but since TRUNCATE is DDL, it means that the old data is not visible to other sessions during the refresh.

Related questions

Update materialized view when urderlying tables change

I have a materialized view defined this way: CREATE MATERIALIZED VIEW M_FOO REFRESH COMPLETE ON COMMIT AS SELECT FOO_ID, BAR FROM FOO WHERE BAR IS NOT NULL GROUP BY FOO_ID, BAR / COMMENT ON MATERIALIZED VIEW M_FOO …

Read More
Oracle - What happens when refreshing a 'REFRESH FORCE ON DEMAND' view with DBMS_MVIEW.REFRESH

I have the following materialized view - CREATE MATERIALIZED VIEW TESTRESULT ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS SELECT... FROM... WHERE... This materialized view has no backing MATERIALIZED VIEW LOG. As seen in …

Read More
How to refresh materialized view using trigger?

create or replace TRIGGER REFRESH_REST_VIEW AFTER INSERT OR UPDATE ON tbl_contract BEGIN execute DBMS_MVIEW.REFRESH('REST_VIEW'); END REFRESH_REST_VIEW; commit; This is my sql trigger i am using to refresh Materialized View. But it …

Read More