Duplicate Table

The duplicate table holds data for every potential duplicate found. If review is turned on for this namespace, the entry is marked as a potential duplicate and sent to a review operator. When the operator reviews and decides if an entry is a duplicate, the entry is flagged as such and marked with the ID of the operator performing the review and the timestamp of the review. The table holds no information specific to the entries being checked for duplicates, but instead represents the entry from the details table. The SYS_ID in the duplicate table corresponds to the SYS_ID field of the item from the details table. If the item in the duplicate table is found to be a duplicate of another item in the details table, the duplicate table SYS_DUP_ID field contains the details table SYS_ID of the duplicate item. This table should be defined as shown below and there should be one for each namespace definition.

CREATE TABLE DUPLICATE
(
   SYS_ID                 BIGINT       NOT NULL,
   SYS_UOW_ID             BIGINT       NOT NULL,
   SYS_DUP_ID             BIGINT,
   SYS_DUP                CHAR(1),
   SYS_ENTERED_DATE       TIMESTAMP,   
   SYS_EXPIRY_DATE        TIMESTAMP,
   SYS_REVIEW_DATE        TIMESTAMP,
   SYS_REVIEW_ID          CHAR(32),
   SYS_REVIEW_LOCK_ID     CHAR(32),
   SYS_REVIEW_LOCK_DATE   TIMESTAMP,
   SYS_ERROR_CODE         CHAR(7),

   CONSTRAINT DUP_PK PRIMARY KEY (SYS_ID)
);

CREATE INDEX DUP_EXPIRY ON DUPLICATE ( SYS_EXPIRY_DATE ) ALLOW REVERSE SCANS;