RE: Flashback query to ensure data consistency

  • From: "Tony Aponte" <Tony_Aponte@xxxxxxxxx>
  • To: <debaditya.chatterjee@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 26 Mar 2008 18:15:17 -0400

The first thing I would say to you is to try the flashback/trigger
strategy you've worked out and see how it affects the user queries.  I
have flashed back up to 10 days and still met query requirements.


Another approach is the Kimball real-time data warehouse strategy that
Dennis Williams already suggested.


A third approach I would pursue is to use Analytic Workspaces.  With
that you can redirect the burden to the ETL workload instead and relieve
the queries from having to indo the transactions.  At the end of the ETL
processing you would use the Workspace features to merge the changes
that have been made to the base tables.


Another approach could be to use Materialized Views and Query Rewrite
against the base tables.  This would give you control over when to
publish the ETL updates by simply refreshing the MVs after the 8-hour
jobs are complete, yet still provide a consistent view of the previous


Tony Aponte



From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Debaditya Chatterjee
Sent: Wednesday, March 26, 2008 4:42 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Flashback query to ensure data consistency



In a 24*7 data warehouse (accessed by users globally from different
timezones), incremental ETL jobs are executed to load data from
different source systems. Due to excessive volume of source data the ETL
window has grown to over 8 hours and users from a particular timezone
(the most affected ones) have started complaining about performance of
the system and also have become very vocal about data inconsistency.
e.g. figures of a sales report can change as the ETL tool commits every
10K records. 

In order to give the users a consistent view of data we are thinking of
using the Flashback query feature of the database. i.e. When the ETL
starts, flashback query will be enabled and the user queries (through a
logon trigger) will run against the flashback data and once the ETL
completes the queries will run against the tables. 

Although the solution seems feasible, but having flashback to work for
more than 8 hours seems to be our biggest challenge. I wanted to verify
if you all agree with this approach or would you suggest using a simpler
(but costly) approach like routing queries to a read only standby
database while ETL jobs execute on the primary database.

Your suggestions are welcome. The oracle database version is,
the ETL tool used is Informatica and the reporting tool used is Siebel


Other related posts: