For example, you archived purchase order data by the purchase order ID. You frequently run queries to find purchase orders for a specific date. When you run the queries, the query performance is slow. You create an index for the purchase order date to improve the query performance.

You can create an index for one or more columns in a table. When you create an index, the Data Vault creates an index file for each data file for which the table has a registration. The Data Vault stores the index files in a directory that you define during the installation or you configure in the

ssa.ini

file. When you run queries, the Data Vault checks if an index exists for the table columns in the query WHERE clause. If an index exists, the Data Vault uses the index to select the data files and to filter the rows.

You can use the standard log or the query statistics log to analyze if the index improves the query performance. The standard log displays the number of data files that the index eliminates as compared to the total amount of data files. For example, the index eliminates 10 data files out of 100 data files. The query statistics log displays the number of data files that the query processes. Use the log files to compare the number of data files that the query processes before and after you create the index.

When you run index commands on a table, you do not affect query access to the table. The Data Vault simultaneously manages index requests and query requests for the same table. Queries only use the updated indexes after the Data Vault completes the index commands.

After you create indexes on tables, you can renew, alter, and drop the indexes. The

ssasql

command line program includes commands to create, renew, alter, and drop indexes.