CODEWITHSUNDEEP
sqloracle-database
Shankar Panda
Shankar Panda

Reputation: 822

Oracle SQL interview Question on Materialized View?

I faced this question on materialized view.

  1. What will happen if a user runs a report which is built on top of a materialized view at the time when the materialized view refresh is going on( refresh method is complete on demand)

my ans was: Complete on demand refresh method will truncate whole data and create again, so user might not see any data in his report at the time of refresh.

  1. If my transactional table is getting updated currently and if i have issued a select statement which runs for around 20 min then will my select statement retrieve old data or it will retrieve newly updated data?

my ans to this: Oracle takes the snapshot of data. When i hit my select statement, it will retrieve the data from that snapshot. So it will retrieve my old data.

Could you please share your knowledge on this.

Upvotes: 0

Views: 1336

Answers (1)

George Joseph
George Joseph

Reputation: 5922

  1. The data would be visible or not based on how the refresh was done. if atomic_refresh=true then the refresh is going to be basically a DELETE + INSERT(ie no TRUNCATE) and so with Oracles feature of read consistency you would get the data as of the time when the select query was fired. If atomic_refresh=false it follows a TRUNCATE + INSERT /+APPEND/ logic so until the refresh was complete you are not allowed to read the table, so it would error out.

Have a look at the following link

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1857127200346321681

  1. You are correct, Oracle gets you the data as of time the select was fired. So even if new rows were added and committed you get the records as it existed when your query began. This is true for regular tables as well. if you run a select count(*) query that takes about 10 minutes to complete and table has 1000 rows, and in the menatime if someone was to add 100 rows and commit into the table during that 10 minutes, the query would return an output of 1000 rows.(This is the case with Oracle)

Upvotes: 2

Related Questions