[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-04-21
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.
Question by:Milleniumaire
  • 2
  • 2
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 35439975
yes, an MV group will be refreshed in consistent state for all members of the group

as long as the group is really a group (not simply a group of one member)

on the down side, if you have lots of data your refreshing transaction can be quite large so watch your rollback/undo limits
LVL 16

Author Comment

ID: 35440125
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.
LVL 74

Expert Comment

ID: 35440202
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.

LVL 16

Author Closing Comment

ID: 35440230
Thanks for the prompt feedback.  This makes me feel better about pursuing this potential solution.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month18 days, 19 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question