Manage indexes

Process Engine (PE) uses indexes to facilitate and speed up searches. When you create a roster, queue, or event log, Process Configuration Console automatically creates the indexes, marked with a System icon. The indexes that PE needs in order to function properly are indicated by the tooltip that appears when you hold the cursor over the icon. You cannot change or delete a mandatory system-defined index.

You can define indexes, depending on the needs of your system. You can add, change, or delete user-defined indexes on rosters, queues, and event logs.

By default, PE creates an index for user queues called PWDefaultOrder. This index determines the order in which a user's work appears in his Inbox. You can modify this index to change the default order.

You should use additional indexes sparingly as they add to the system overhead, both in space and performance. Indexes are the primary means of reducing disk I/O when properly used. Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts because the system must also update the indexes associated with the table.

You can create simple or composite indexes. A composite index (also called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any order. Composite indexes can speed retrieval of data for search filters in which the filter conditions reference all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important; generally, the most commonly accessed or most selective columns go first.

An index must not exceed the maximum index key size.

To change an index

  1. If the Properties dialog box is not already displayed, select the icon of the queue, roster, or event log you wish to modify and click Properties on the toolbar.
  2. Select the Indexes tab.
  3. Select the user-defined index you wish to change.
  4. Click Edit.
  5. To add fields to the index, select one or more items in the Available items list and click Add.

    To remove fields, select one or more items in the Selected items list and click Remove.

  6. To change the order of the indexed fields, select one or more field names from the Selected items list, then click Up or Down.
  7. Click OK when done.
  8. Click Commit Changes on the toolbar to apply this change to your isolated region. See Commit changes for additional information.

To add an index

  1. On the Indexes tab, click New.
  2. Enter the name of the index in the Index name box. The index name must meet PE naming requirements.

    TIP The name you enter is a logical name. PE dynamically determines the physical table and index names, to prevent collisions with existing table and index names.

  3. To add fields to the index, select one or more items in the Available items list and click Add.

    To remove fields, select one or more items in the Selected items list and click Remove.

  4. To change the order of the indexed fields, select one or more field names from the Selected fields list, then click Up or Down.
  5. Click OK to close the New Index dialog box.
  6. Click OK when done.
  7. Click Commit Changes on the toolbar to apply this change to your isolated region. See Commit changes for additional information.
To delete an index
  1. On the Indexes tab, select one or more indexes you wish to delete. You cannot delete mandatory system-defined indexes, indicated by the tooltip associated with the System icon.
  2. Click Delete.
  3. Click Yes or Yes to All to confirm.
  4. Click OK when done.
  5. Click Commit Changes on the toolbar to apply this change to your isolated region. See Commit changes for additional information.

     

NOTES 

Some system indexes appear with no index key fields defined. The fields actually indexed are not available for querying, and therefore do not appear in the list.

A roster index must contain the F_WobNum field. If this field is not included in your indexed fields, it will automatically be added for you when you close the Index Properties dialog box.

An event log index must contain the F_TimeStamp and F_SeqNumber fields. If these fields are not included in your indexed fields, they will automatically be added for you when you close the Index Properties dialog box.

F_QueueWPClassId should not be used as the first or only field for an index on a queue because the system already uses this index internally for cases where two queues share the same physical table.