|
A single publication provides the basis for the data sharing:
CREATE PUBLICATION SalesRepData (
TABLE SalesReps,
TABLE Policy SUBSCRIBE BY rep_key,
TABLE Customers SUBSCRIBE BY (
SELECT rep_key FROM Policy
WHERE Policy.cust_key =
Customers.cust_key
),
);
The subscription statements are exactly as in the previous example.
The publication includes part or all of each of the three tables. To understand how the publication works, it helps to look at each article in turn:
SalesReps table There are no qualifiers to this article, so the entire SalesReps table is included in the publication.
... TABLE SalesReps, ...
Policy table This article uses a subscription expression to specify a column used to partition the data among the sales reps:
... TABLE Policy SUBSCRIBE BY rep_key, ...
The subscription expression ensures that each sales rep receives only those rows of the table for which the value of the rep_key column matches the value provided in the subscription.
The Policy table partitioning is disjoint: there are no rows that are shared with more than one subscriber.
Customers table A subscription expression with a subquery is used to define the partition. The article is defined as follows:
...
TABLE Customers SUBSCRIBE BY (
SELECT rep_key
FROM Policy
WHERE Policy.cust_key =
Customers.cust_key
),
...
The Customers partitioning is non-disjoint: some rows are shared with more than one subscriber.
The subquery in the Customers article returns a single column (rep_key) in its result set, but may return multiple rows, corresponding to all those sales representatives that deal with the particular customer. When a subscription expression has multiple values, the row is replicated to all subscribers whose subscription matches any of the values. It is this ability to have multiple-valued subscription expressions that allows non-disjoint partitionings of a table.