Re: Flashback query to ensure data consistency

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_at_gmail.com> 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.
>

--
http://www.freelists.org/webpage/oracle-l