|
|
| |
 |
|
Oracle Tips
by Burleson Consulting
|
The Data Warehouse Development Life Cycle
Distributed Oracle Data Warehouses
Table Replication With Oracle Snapshots
Oracle snapshots are used to create read-only copies of tables in
other Oracle databases. This is a highly effective way to avoid
expensive cross-database joins of tables. As you probably know, an
SQL join with a table at a remote server is far slower than a join
with a local table because SQL*Net overhead increases as it
retrieves and transfers data across a network.
Note: For more information about table replication utilities see
Chapter 10, Oracle Data Warehouse Utilities.
It is interesting to note that the general attitude about data
replication has shifted dramatically in the past 10 years. In the
1980s, replication was frowned upon. Database designers believed
that there was no substitute for the third-normal form database.
Today, the practical realities of distributed processing have made
replication a cheap and viable alternative to expensive
cross-database joins.
Table replication is so stable and has been so successful within
Oracle version 7 that Oracle is now introducing the concept of
updatable snapshots with Oracle version 7.3. However, replication is
not to be used indiscriminately, and the following guidelines exist
for using replicated tables to the best advantage:
* Replicated tables should be read-only. Obviously, a table
snapshot cannot be updated because the master copy of the table is
on another server.
* Replicated tables should be relatively small. Ideally, a
replicated table is small enough that the table can be dropped and
re-created each night, or the REFRESH COMPLETE option can be used.
Of course, large tables can be replicated with the REFRESH FAST
option, but this involves a complicated mechanism for holding table
changes and propagating them to the replicated table.
* Replicated tables should be used frequently. It does not
make sense to replicate a table if it is only referenced a few times
per day, and the cost of the replication would outweigh the cost of
the cross-database join.
Despite any claims by Oracle to the contrary, snapshots are not to
be used indiscriminately. Only tables that meet the above criteria
should be placed in snapshots. In practice, snapshots are not
maintenance-free, and many points of failure are
possible--especially if the snapshot is created with the REFRESH
FAST option. Problems can occur while writing to the SNAPSHOT_LOG
table, and SQL*Net errors can cause update failures to transfer to
the replicated tables.
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|
