You can increase the performance of policy-based and job
sweeps by creating a covering index on the base table you are sweeping.
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.
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);