Oracle Distributed Systems

Subquery Subsetting

Subquery subsetting is one of the most significant feature additions to replication in Oracle8. This is a method that allows you to create snapshot sites containing only the data that is locally relevant without having to have a distinguishing key in every table for which you create a snapshot. For example, an order fulfillment center might process orders only from customers in California, yet it needs data from tables CUSTOMERS, ORDERS, and ORDER_ITEMS.

The challenge is to create snapshots of the ORDERS and ORDER_ITEMS tables that contain data for the California customers only. However, the ORDERS table has a customer_id field and no state field, while the ORDER_ITEMS table doesn’t even have a customer_id field. In other words, the schema is normalized. Clearly adding and maintaining a state field in the ORDERS and ORDER_ITEMS table would be awkward at best.

Rather than denormalize the schema by putting the state field in all of the tables for which you created a snapshot, we can use a subquery subset snapshot, which takes advantage of foreign keys defined on the tables to determine which records of the ORDERS and ORDER_ITEMS tables the snapshot site needs to see. We can create the snapshots as follows:

1. Create a snapshot on the CUSTOMERS table containing only the records where state = CA:

   CREATE SNAPSHOT customers REFRESH FAST START WITH sysdate NEXT sysdate + 1 AS SELECT customer_id, sales_rep_id, first_name, last_name, addr_line_1, addr_line_2, city, state, zip ...