Indexing the ifx_replcheck Column

You can index the ifx_replcheck shadow column to increase the speed of consistency checking.

Before you begin

If you have a large replicated table, you can add the ifx_replcheck shadow column and then create a new unique index on that column and the existing replication key columns. The index on the ifx_replcheck shadow column allows the database server to determine whether rows in different tables have different values without comparing the values in those rows. You must create the index on the table in each database server that participates in the replicate.

Before you can create an index on the ifx_replcheck shadow column and the replication key columns, you must prepare the replicated table by adding the ifx_replcheck shadow column. You can add the ifx_replcheck shadow column when you create the table with the WITH REPLCHECK clause, or you can alter an existing table to add the ifx_replcheck shadow column with the ADD REPLCHECK clause.

About this task

You can create the index while replication is active.

Procedure

To index the ifx_replcheck shadow column, create a unique index based on the existing replication key columns and the ifx_replcheck column. The ifx_replcheck shadow column must be the last column in the index.

Example

For example, the following statement creates an index on a table named customer on the primary key column id and ifx_replcheck:

CREATE UNIQUE INDEX customer_index ON  customer(id, ifx_replcheck);