O'Reilly logo

Oracle Distributed Systems by Charles Dye

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required