| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Distributed Oracle Data Warehouses
REAL-WORLD TIPS AND TECHNIQUES FOR ORACLE SNAPSHOTS
Even with Oracle’s distributed features, it is still far faster to
process a table on a local host than it is to process a remote table
across SQL*Net’s distributed communication lines. As such, table
replication is a very desirable technique for improving processing
speeds.
Several factors influence the decision about replicating tables. The
foremost considerations are the size of replicated tables and the
volatility of the tables. Large, highly active tables with many
updates, deletes, and inserts require a lot of system resources to
replicate and keep the tables synchronized with the master table.
Smaller, less active tables are ideal candidates for replication,
because the creation and maintenance of the tables will not consume
a high amount of system resources.
Oracle’s snapshot facility is relatively mature, and it generally
works as noted in the Oracle documentation. However, the flexibility
of the snapshot tool gives developers many choices in how snapshots
can be created and refreshed. Developers can refresh a replicated
table in full, re-create a snapshot at will, choose periodic
refreshes of a snapshot, and use database triggers to propagate
changes from a master table to a snapshot table. While the choice of
techniques depends on individual applications, some general rules
apply.
If a replicated table is small and relatively static, it is usually
easier to drop and re-create a snapshot than to use Oracle’s REFRESH
COMPLETE option. A crontab file can be set up to invoke a drop and
re-create at a predetermined time each day, completely refreshing
the entire table.
Another popular alternative to the snapshot is using Oracle’s
distributed SQL to create a replicated table directly on the slave
database. In the following example, the New York database creates a
local table called EMP_NY, which contains New York employee
information from the master employee table at corporate
headquarters:
|
