Database indexes for index jobs

Without the appropriate database indexes on various tables such as the DocVersion table, some types of index jobs can run slowly. For example, the DocVersion table has millions of rows if your object store has millions of documents.

The creation of a database index for a large table might take several hours. Do not create a database index while an index job is in progress. If an index job is running, pause the job before you create the database index.

Although database indexes can improve index job performance, they can also slow object creation and object updates. You might want to create the appropriate database indexes on a temporary basis in the following manner:
  1. Create the database index
  2. Run the index job
  3. Drop the database index
The decision to create the database indexes permanently or temporarily depends on how often you run the relevant index jobs. Most likely, you want to create the database indexes on a temporary basis.
Table 1. Database indexes for various types of index jobs
Index job type SQL example
For full-text index Create a database index in the following manner:
(CREATE INDEX I_INDEXATION_DOC 
ON DOCVERSION ("INDEXATION_ID" ASC, "OBJECT_ID" DESC))

(CREATE INDEX I_INDEXATION_ANO 
ON ANNOTATION ("INDEXATION_ID" ASC, "OBJECT_ID" DESC))

(CREATE INDEX I_INDEXATION_GEN 
ON GENERIC ("INDEXATION_ID" ASC, "OBJECT_ID" DESC))

(CREATE INDEX I_INDEXATION_CON 
ON CONTAINER ("INDEXATION_ID" ASC, "OBJECT_ID" DESC))
For base class Create a database index in the following manner:
CREATE INDEX index_name 
ON DocVersion (object_class_id, object_id)
In some cases, this database index can reduce the run time for an index job from days to minutes.