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

Posted on 2011-04-21
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
    LVL 73

    Accepted Solution

    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

    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 73

    Expert Comment

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now