Managing Indexes

As of Release 2021 R2.1, DataSync allows you to manage the creation, update, and deletion of indexes in the destination table based your extractions. Indexes are automatically created when retrieving source tables. While indexes improve the performance of selections and joins, they take additional space and can slow performance of database inserts, updates, and deletes.

Indexes are only available in extractions of type Synchronization and Consolidation.

Adding Indexes

To create an additional index after adding a table:

On the Extractions page, click the table link in the Tables column for an extraction of type Consolidation or Synchronization.
On the Tables page, click the link in the Index column.
On the Indexes page, click Add.
In the Add Index dialog box :
1. Configure the index as described in the table below.
2. Add fields by clicking the + icon.
3. Click Add.
Click Save on the Index page.

Setting	Description
Description	Index description.
Type (SQL Server only)	Specifies the index type: Non Clustered: You can create several Non Clustered indexes. Clustered: There can be only one clustered index per table, because the data rows can be stored in only one order. Clustered Columnstore: This is a type of Columnstore index. A Clustered ColumnStore index cannot have any fields in it. There can be only one Clustered Columnstore index per table. Non Clustered Columnstore: This is a type of Columnstore index. You can create several Non Clustered indexes. A Non Clustered Columnstore index cannot have sorted fields.
Unique	Unique indexes help maintain data integrity by ensuring that there are no rows of data in a table with identical key values. The option must be selected when the source collation is different than the destination collation. To enable this option, click the Is Unique check box in the Add Index or Edit Index dialog box. Note: A (Clustered or Non Clustered) Columnstore index cannot be set as Unique.
Fields	Identifies the number of fields created in the index. The order of the fields will be the order in the database. Only synchronized fields are available to choose from in the drop-down list. A field cannot be used in an index twice. Click the row in the Fields column to select a field from the drop-down list. Set the order of fields by using the drag and drop icon in the first column. Choose the Sort Order of each field (Ascending or Descending). Note The maximum number of fields that can be added is 32 for a Synchronization and 31 for a Consolidation. For SQL Server 2014 or less, the maximum number of fields is 16 for a Synchronization and 15 for a Consolidation. Also, the maximum number of bytes per index key for SQL Server is 900 bytes for a clustered index and 1,700 bytes for a non-clustered index. Refer to these links to know more about database limitations: SQL Server 2016 or higher: https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server SQL Server 2014: https://docs.microsoft.com/en-us/previous-versions/sql/2014/sql-server/maximum-capacity-specifications-for-sql-server Oracle 11g: https://docs.oracle.com/cd/E11882_01/server.112/e40402/limits003.htm Oracle 12c: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/logical-database-limits.html
List of fields	List of selected fields in the Index page.

Building indexes in the destination

New indexes will be created and existing indexes will be modified.

To build indexes for a consolidation:

Select an extraction of type Consolidation.
Perform a Validate and Build with the Drop the previously created object... option selected.

To build indexes for a synchronization:

Select an extraction of type Synchronization.
Perform a Validate and Build with the Drop the previously created object... option selected.
Perform a Run Extraction Now in Truncate and Load mode.

Updating Indexes

To update an index:

Click the table link in the Tables column for an extraction of type Consolidation or Synchronization.
Click the link in the Index column.
Click Edit on the toolbar.
Make the required changes.
Click Confirm.
Click Save.

To add a newly-created index or update an existing index without reloading all the data:

Select an extraction of type Consolidation or Synchronization.
Perform a Validate and Build with the Only Add Missing Tables/Fields/Indexes option selected.

New indexes will be created and existing indexes will be modified.

Note

During the Validate and Build process, you may encounter a timeout error when creating new indexes on an existing table with a lot of data. However, the query is still running in the backend and the updates can be viewed in the Logs page. If you wish to avoid the display of this error, increase the requestTimeout parameter in the web.config file of DataSyncAPI.

Deleting Indexes

To delete an existing index:

Click the table link in the Tables column for an extraction of type Consolidation or Synchronization.
Click the link in the Index column.
Select the index.
Click Delete in the toolbar.
Click Confirm.
Click Save.

To delete an index that was created in a destination database:

Delete it from DataSync as described above.
Run a Validate and Build with the Only Add Missing Tables/Fields/Indexes option selected. Indexes that were deleted in DataSync will be deleted from the destination table.