We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Provide ability to report on consistent set of data while it is changing

Medium Priority
Last Modified: 2013-11-11
We are in the process of building a new data warehouse using Oracle 11g.

Most of the new data will be loaded into the data warehouse overnight, however, our users have decided they want to be able to report on the DW 24 x 7!

Our dilemma is how to make the DW available for reporting (via a Business Objects Universe), while loading new data into it, and ensuring the reports produce results that are correct.

Our current thinking is that we will build a Data Mart from the DW and refresh this Data Mart each night after all data has been loaded into the DW.  The data in the data mart will only show the "current" view of data i.e. it will not contain all historical data.  The BO universe will then be based on the Data Mart, rather than the DW.

However, if a report is running against tables in the Data Mart when the Data Mart is refreshed, this may affect the reports.

We are considering using materialized views (instead of standard tables in the data mart), which will provide for a simpler refresh mechanism.  If a materialized view group is created, refreshing the group will refresh all data in the data mart.  Simple!!!

Is it possible to refresh a materialized view while queries are running against it?  (I know I could try this, but just don't have the time).  If this is possible, is Oracle clever enough to provide a read-consistent view of a materialized view to running queries, and to then allow newly started queries to see the new data in the view?  If this is possible, then hopefully refreshing a materialized view group will provide a read consistent view for ALL data in ALL materialized views in the group?

I suspect we are expecting a lot from Oracle, but it must be possible to provide 24 x 7 reporting, while data is being changed.

I would appreciate any comments or suggestions on how to achieve our aims based on your experiences of doing this.
Watch Question

Database Developer & Administrator
Most Valuable Expert 2011
Top Expert 2012
Unlock this solution and get a sample of our free trial.
(No credit card required)


Thanks sdstuber, I was hoping that was the case.

Just to confirm, if a number of queries are running against various materialized views in a schema and these views all belong to the same group, refreshing the group will start straight away i.e. it won't wait for the queries to finish.  Once the refresh has completed, new queries will see the newly refreshed data, but existing queries will continue to run against the old data (using rollback/undo).

Thinking about it, I guess this is no different to deleting the data from a number of tables and then re-inserting it (all as a single transaction).  Existing queries continue to see a (older) consistent state of the data and after the transaction is committed, new queries will report against the new data.
Sean StuberDatabase Developer & Administrator
Most Valuable Expert 2011
Top Expert 2012

within an MV group,  when you refresh it

if it's complete you are effectively doing...

delete from table1;
insert into table1 select * from....;
delete from table2;
insert into table2 select * from....;
delete from table3;
insert into table3 select * from....;

if it's fast then you're doing the same thing but you have updates and individual row deletes instead of full deletes.

Even if you have some complete, some fast and some force they are still refreshed inside a single transaction.

And because it's Oracle, all queries already running will be kept consistent with respect to the before-refresh data.

The only reason I mentioned the "really a group" part above is, a complete refresh of a single MV, even if in a group will usually start with a truncate then insert.  Which will cause the MV to be empty until the refresh completes.  For multiple-MV groups, truncate isn't an option because it would break consistency so even a complete refresh is done with delete first.


Thanks for the prompt feedback.  This makes me feel better about pursuing this potential solution.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.