Re: Flashback query to ensure data consistency

  • From: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
  • To: debaditya.chatterjee@xxxxxxxxx
  • Date: Wed, 26 Mar 2008 15:54:22 -0500

Deba,

Are you using partitioning? Tim Gorman has a seminar that discusses how
echange partition can be used to great advantage for just this situation.
Basically you have a daily partition that you prepare as a table, then when
it is complete, you exchange it with a partition. Unfortunately these
methods work best when designed from the start, so probably not an immediate
help to you, but something to keep in mind going forward.

Dennis Williams


On 3/26/08, Debaditya Chatterjee <debaditya.chatterjee@xxxxxxxxx> wrote:
>
> All,
>
> 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 10.2.0.3, the
> ETL tool used is Informatica and the reporting tool used is Siebel
> Analytics.
>
> Thanks
> Deba.
>

Other related posts: