CODEWITHSUNDEEP
sqloracle-databasematerialized-views
jo_wil
jo_wil

Reputation: 369

Oracle materialized view refresh data availability

In an application with Oracle materialized views I am curious about data availability during a refresh.

I will describe a potential example.

A materialized view is created that refreshes every hour on the hour. The query takes 1 minute to run to refresh this view. In the meantime a set of APIs use this view to retrieve data and could potentially write to an underlying table if that makes a difference.

My questions are as follows:

  1. What happens to queries during that 1 minute refresh time, are they blocked? Do they get an old copy of the data?
  2. What happens to writes to the underlying tables during this time, are they affected in any way.
  3. What if an active query comes in a 2:59:59 and runs for 30 seconds on the materialized view, does this lock the view and refresh would not start till 3:00:29 or does that query get interrupted and paused by the refresh?

Upvotes: 2

Views: 1680

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

The rules for what data queries see in a materialized view are identical to the rules that apply to what data queries see in a table that is undergoing changes. Those rules are summed up in the documentation under multiversion read consistency.

I will assume that you are doing an atomic refresh of the materialized view. You could do a non-atomic refresh which would truncate the materialized view before refreshing but that is almost certainly not what you'd want in this case.

  1. Queries would see the data as it existed when the query started (or when the transaction started if the session is in a serializable transaction isolation level). So it would see a consistent picture of the data before the refresh started.
  2. Readers don't block writers so there would be no impact on writes to the underlying table.
  3. Same as 1, the query would see a consistent picture of the data as it existed when the query started running (or when the transaction started). The refresh would run at the same time just in a separate transaction scope. The only impact is that the query would potentially be marginally slower because it would likely need to apply some amount of UNDO to currently cached blocks in order to return the data as it existed when the query started.

Upvotes: 4

Related Questions