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.
This is an excerpt from "High Performance
Data Warehousing", copyright 1997.
||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.
can buy it directly from the publisher and save 30%, and get instant
access to the code depot of Oracle tuning scripts.