Distributed Oracle Data Warehouses Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Distributed Oracle Data Warehouses
REAL-WORLD TIPS AND TECHNIQUES FOR ORACLE SNAPSHOTS

CREATE TABLE emp_ny
AS
SELECT
emp_nbr,
emp_name,
emp_phone,
emp_hire_date
FROM emp@hq WHERE department = ‘NY’;

Very large replicated tables consume too much time in dropping and re-creating the snapshot or using the REFRESH COMPLETE option. For static tables, a snapshot log would not contain very many changes--you could direct Oracle to propagate the changes to the replicated table at frequent intervals. Let’s take a look at some different refresh intervals that can be specified for a snapshot.

In the first example, Oracle is instructed to take the snapshot log and apply it to the replicated table every seven days:

CREATE SNAPSHOT cust_snap1
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+7
AS SELECT cust_nbr, cust_name FROM customer@hq SEE CODE DEPOT FOR FULL SCRIPT

The next example shows a table that is refreshed each Tuesday at 6:00 AM:

CREATE SNAPSHOT cust_snap1
REFRESH FAST
START WITH SYSDATE
NEXT NEXT_DAY(trunc(sysdate),’TUESDAY’)+6/24
AS SELECT cust_nbr, cust_name FROM customer@hq WHERE department = ‘NY’;

---

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.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.