System Administration

Build the Index

Once an index has been configured, before it can be used:

  • The index objects must be built.
  • The index must be populated.

Before an index can be built, it must be created, configured, if needed and have at least one searchable column.

This topic contains the following sections:

Build Index Objects

An index is comprised of several SQL Server objects, including three tables to store the index and four stored procedures to populate the index. Before a table can be indexed, the objects must be built based on the index configuration. Building the index objects is generally a one-time event. If the index configuration changes, the objects must be rebuilt.

To build the index objects:

  1. Select Admin > Data Sources in the Navigation pane.
  2. Click the Index icon for a data source.
  3. Click the Build icon in the Page toolbar to build the selected index.

The build process creates three tables in the user database: TableName#SearchKey, TableName#Search and TableName#SearchWord, where TableName is the name of the indexed table. The tables comprise a reverse index of the records in the table.

  • The #SearchKey Table: Because the source table may not have an optimal primary key structure, the primary keys for each record are stored in the #SearchKey table. Each key is assigned a new ID using a SQL Server IDENTITY column called boaKeyID.
  • The #SearchWord Table: Every unique word in the source table contains a record in the #SearchWord table. In addition to the word, a pre-computed SoundEx value is stored with the word. The word is assigned an ID, boaIndexID, which is a SQL Server IDENTITY column.
  • The #Search Table: The #Search table is the reverse index for the table and associates individual words with records. A word is paired to a record once. If the word appears in the record more than once, a count is reflected in the Occurrences column. The IsSynonym column designates whether the word appears in the record or whether a synonym of the word appears in the index.

In addition to the index tables, several stored procedures are created for managing the index:

  • boaIndex_TableName_KeyFilterExecuteLoop
  • boaIndex_TableName_Loop.
  • boaIndex_TableName_NoFilterExecuteLoop
  • boaIndex_TableName_PartialExecuteLoop

These procedures are used by the internal indexing routines and should not be modified directly.

Populate an Index

Once an index has been built, it can be populated with data. This step is required before users can search the index or before duplicate detection can take place. Once the index has been fully populated, incremental populations are performed as records are modified.

To populate an index:

  1. Select Admin > Resources > Indices in the Navigation pane.
  2. Click Start, a confirmation message displays.
  3. Click OK.

    NOTE: Once the table has been submitted to be indexed, a confirmation message displays.

  4. Click OK.

The job itself can be monitored by clicking All Jobs. The duration of the job depends on the amount of data in the table.

The first stage of the full index process queues up records to index. The records are then indexed in batches. Each batch is treated as a step. The total number of steps is determined by the number of records.

To monitor the individual indexing process’s steps, click Vertical View.

Full index population can occur on a schedule. A schedule is important if data in the source table is re-imported nightly by an external source. Only Active indices receive a full index. The ACTIVE check box on the Indices page indicates the index is active.

A service page in the Stewardship Tier performs full indices. By default, the service page executes once a day.

Stop a Running Index

Indexing a table can take a long time, so the option to stop a running indexing is available.

To stop a running index:

  1. Select Admin > Resources > Indices in the Navigation pane.

    NOTE: If the index is running, the status displays as Queued and the Stop button is enabled.

  2. Click Stop to cancel indexing.