Indexes

Use the Indexes page to create, update, or delete indexes in the destination table for your extractions. Indexes are automatically created when source tables are retrieved.

Indexes can improve performance for selections and joins, but they require additional storage and can slow inserts, updates, and deletes. After adding, updating, or deleting indexes, always validate, build, and run the extraction to apply changes to the destination table. Indexes are available only for synchronization and consolidation extractions.

To refresh the index list, select Refresh in the upper-right corner of the page.

Column overview

Column	Descrciption
Description	Displays the index name.
Type	Shows the index type: Non Clustered – Multiple non-clustered indexes can be created. Clustered – Only one clustered index is allowed per table because data rows can be stored in only one order.
Unique	Indicates if the index is unique. A unique index ensures no duplicate key values in a table. This must be selected when the source collation differs from the destination collation. Enable by selecting Is Unique in the Add or Edit Index dialog.
Fields	Displays the number of fields in the index. Field order here determines index order in the database. Only synchronized fields can be added, and each field can appear only once.

Create additional indexes

1. From the welcome screen or left navigation pane, select Extractions.
2. Select a synchronization or consolidation extraction from the list.
3. Click the link under the Tables column.
4. Select a table, then click the link under the Index column.
5. Click Add.
6. Enter a description for the index.
7. In Index Type, select Non Clustered or Clustered.
8. (Optional) Select Is Unique to create a unique index.
9. Select the + icon to add index fields, then for each field specify:
   • Order – The sequence the field appears in the index.
   • Fields – The field to index.
   • Sort Order – None, Ascending, or Descending.
10. Click Add to create the index.
11. In the Indexes page, click Save to apply changes.

Build indexes in the destination

Use this procedure to create new indexes and update existing ones in the destination table based on changes to your extraction’s index definitions.

For synchronization extraction

1. Select a synchronization extraction from the list.
2. Click Validate and Build in the upper-right corner.
3. Select Drop the previously created object and recreate the objects based on the new definition.
4. Click Build.
5. With the same extraction still selected, click Run Extraction Now in the upper-right corner.
6. Select Truncate and Load in the dropdown list.
7. Click Run.

For consolidation extraction

1. Select a consolidation extraction from the list.
2. Click Validate and Build in the upper-right corner.
3. Select Drop the previously created object and recreate the objects based on the new definition.
4. Click Build.

Update an index

Edit existing indexes

1. In the Extractions page, select a synchronization or consolidation extraction.
2. Click the link under the Tables column.
3. Select a table, then click the link under the Index column.
4. Select an index and click the pencil icon in the upper-right corner.
5. Make the required changes.
6. Click Confirm.
7. Repeat for any other indexes.

Build missing indexes

1. In the Extractions page, select the extraction with the updated indexes.
2. Click Validate and Build in the upper-right corner.
3. Select Only Add Missing Tables/Fields/Indexes.
4. Click Build.

note

Creating new indexes on a large existing table may cause the Validate and Build process to display a timeout error. The operation will continue running in the background, and its progress can be monitored in Logs. To prevent this message, increase the requestTimeout parameter in the web.config file of DataSyncAPI.

Delete an index

1. In the Extractions page, select a synchronization or consolidation extraction.
2. Click the link under the Tables column.
3. Select a table, then click the link under the Index column.
4. Select an index and click the trash icon in the upper-right corner.
5. In the confirmation message, click Confirm.
6. Repeat for any other indexes you want to delete.
7. Return to the Extractions page and select the extraction where you deleted indexes.
8. Click Validate and Build in the upper-right corner.
9. Select Only Add Missing Tables/Fields/Indexes to remove the deleted indexes from the destination table.
10. Click Build.