Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Distributed Oracle Data Warehouses Warehouse Table Partitioning And Distributed Oracle

In data warehouses that allow cross-database access, a very common method of distribution uses horizontal partitioning. For example, customer service organizations commonly allow their remote sites to maintain customer information while maintaining a location-transparent access mode to every customer, regardless of their physical location. Horizontal partitioning is achieved by taking a subset of each remote site's customer table and populating a master look-up table that is accessible from any node in the distributed data warehouse, as shown in Figure 9.10.

Figure 9.10 Horizontal data partitioning.

In a Unix-based distributed data warehouse, the CRON utility can be used to schedule a periodic refresh of a master table. CRON is a time-dependent task activation utility that starts tasks at predetermined dates and times. An SQL script automatically extracts customer_name from the remote site and repopulates the master customer table, leaving the customer details at the remote site. The Oracle SQL might look like this:

/* Delete remote rows in the master table... */

DELETE FROM customer@master
SEE CODE DEPOT FOR FULL SCRIPT;

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.