Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Distributed Oracle Data Warehouses Real-time table updates

An alternative to refreshing our replicated tables once each day is to synchronize the master database with the slave database and have all updates instantly propagated to all replicated tables. The choice between refreshing and synchronization depends on the volatility of the data and the currency needs of the user community. For example, a static, non-changing database can easily be refreshed daily, provided that the end users understand that their data is only current to within 24 hours. For highly dynamic databases with constantly changing information, a synchronization scheme can be developed, but it is important to remember that there can be tremendous overhead when updating a replicated database. The overhead is especially noticeable when system indexes are updated. Indexes share a common root node, so performance can degenerate if updates are being applied while an index tree reconfigures itself to accommodate new data. Speaking from experience, it is often faster to refresh a small database hourly than to incur the performance problems that accompany real-time updating.

Some warehouses choose to completely refresh replicated tables. This is done by either dropping and re-copying the replicated table or using the REFRESH COMPLETE clause of the CREATE SNAPSHOT statement.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.