The following simplified example explains why, if the history table only tracks changes in certain columns, each replica database is still updated with all required changes to the data:
Suppose that your replica database contains information only for customers of the London branch office. Your replica must be notified of each of the following types of changes:
1 Changes to information about customers of the London office (for example, changes to customer phone numbers).
2 Addition of new customers to (or deletion of old customers from) the London office. For example, if you open up a Liverpool office and assign the Liverpool office some customers who were formerly assigned to the London office, then those customers should no longer be received by the London office when it requests refreshes of updated data. In fact, the master database must explicitly notify the London replica database that it should delete its copy of information about those customers.
In other words, there are changes to records within a set (the London set), and there are changes that cause records to move from one set to another set (for example, from the London set to the Liverpool set). Each of these types of change is tracked by using a separate mechanism.
Changes within a set are tracked by using the general versioning system of the server. For example, if the London office last refreshed data at 12:01 AM December 3, 2021, then the London office must have updates for any records that are in the London set and that have changed since 12:01 AM December 3, 2021.
Changes that affect the set in which a record appears (London, Liverpool, and so on) are tracked in the history table. Since the history table only needs to track addition of or removal of customers from a particular set, it only needs to track changes to the fields that determine which set the record is in. Those fields are the search criteria (the WHERE clause and the join fields that you specified when you defined the publication). Since the only way that a customer record can be added to or removed from, the London branch publication is if there is a change to one of the columns used in the WHERE clause (or the join clause), the history table only needs to record changes to values in those columns.
For example:
CREATE TABLE customer ( id VARCHAR NOT NULL PRIMARY KEY, name VARCHAR NOT NULL, salesman_id VARCHAR NOT NULL) ;
CREATE TABLE invoice ( customer_id VARCHAR NOT NULL, invoice_number VARCHAR NOT NULL, invoice_date DATE NOT NULL, invoice_total NUMERIC (12,2), PRIMARY KEY (customer_id, invoice_number));
CREATE PUBLICATION customers_by_salesman (salesman_id VARCHAR) BEGIN RESULT SET FOR customer BEGIN SELECT * FROM customer WHERE salesman_id = :salesman_id ; RESULT SET FOR invoice BEGIN SELECT * FROM invoice WHERE customer_id = customer.id ; END END END
To optimize this set of tables for update performance, the following statements should be executed:
ALTER TABLE customer SET HISTORY COLUMNS (salesman_id) ALTER TABLE invoice SET HISTORY COLUMNS (customer_id)
Important If you execute the statement ALTER TABLE ... SET HISTORY COLUMNS, but you make a mistake and do not specify all the appropriate columns, then the refresh mechanism will not work properly, and records might not be stored in the proper replica databases. Each time you add a new publication, you must consider whether you need to execute the ALTER TABLE ... SET HISTORY COLUMNS statement again to take into account additional columns that are used in the search criteria of the new publications.
The ALTER TABLE ... SET SYNC HISTORY statement is never required. If the HISTORY COLUMN property is not set for a table, the synchronization will work properly but might not be optimized for performance.