Creating an index to improve sweep performance

You can increase the performance of policy-based and job sweeps by creating a covering index on the base table you are sweeping.

About this task

A covering index must contain all columns that are included in the select list of the database sweep query, and must define OBJECT_ID as a unique value. Including all columns in the covering index ensures that the sweep query traverses only the index, and does not access the underlying data pages.

Procedure

To create a covering index, do one of the following steps:

For DB2 and SQL Server, run the CREATE UNIQUE INDEX SQL command, specifying that the rows should be sorted in ascending order:

CREATE UNIQUE INDEX index_name ON table_name (id_column_name ASC)
    INCLUDE (column_name_1, column_name_2, ...);

Example:

CREATE UNIQUE INDEX DV_COVER_1 ON DOCVERSION (OBJECT_ID ASC)
    INCLUDE (OBJECT_CLASS_ID, CONTENT_RETENTION_DATE, SECURITY_ID,
    EPOCH_ID, HOME_ID, VERSION_STATUS, SECURITY_FOLDER_ID,
    RECOVERY_ITEM_ID);

For some tables, Content Platform Engine uses a clustered index on OBJECT_ID. If a clustered index is used on the table you want to sweep, then a covering index might not be needed.

For Oracle, the columns need to be part of the index itself. Run the CREATE UNIQUE INDEX SQL command:

CREATE UNIQUE INDEX DV_COVER_1 ON DOCVERSION (OBJECT_ID ASC,
    OBJECT_CLASS_ID, CONTENT_RETENTION_DATE, SECURITY_ID,
    EPOCH_ID, HOME_ID, VERSION_STATUS, SECURITY_FOLDER_ID,
    RECOVERY_ITEM_ID);

Sweeps that limit the classes that are examined must include OBJECT_CLASS_ID in both the SELECT list and the WHERE clause. In most cases, creating a covering index with the OBJECT_ID as the first value provides adequate performance. Typically, the sweep traverses the OBJECT_ID in the index, and looks up the OBJECT_CLASS_ID from the index, without accessing the data pages. In some cases, however, only a small number of objects match the target class of the sweep. In those cases, a covering index on OBJECT_CLASS_ID + OBJECT_ID that includes the columns of the selection list can be used. For example:
```
CREATE UNIQUE INDEX DV_COVER_1 ON DOCVERSION (OBJECT_CLASS_ID ASC,
    OBJECT_ID ASC) INCLUDE (CONTENT_RETENTION_DATE, SECURITY_ID,
    EPOCH_ID, HOME_ID, VERSION_STATUS, SECURITY_FOLDER_ID,
    RECOVERY_ITEM_ID);
```