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