Available with Standard or Advanced license.
Attribute indexes can speed up attribute queries on feature classes and tables. An attribute index is an alternate path used by ArcGIS to retrieve a record from a table. For most types of attribute queries, it is faster to look up a record with an index than to start at the first record and search through the entire table.
Once you have data in a feature class or table, create attribute indexes for the fields you frequently query against. Create only those indexes you really need, since each index you add slightly slows edits to the feature class. Each time you edit the feature class, ArcGIS must also update the indexes. If you need to frequently edit a field, avoid creating an index for it if you can. Once an index has been added, it can be deleted and added again at any time.
Attribute indexes can be created in different ways. They can be created for single or multiple fields; they can be unique; and for some geodatabases, they can be created in ascending or descending order. This topic provides only a brief introduction to these concepts. If you're choosing an indexing strategy for a geodatabase, refer to your DBMS documentation for more detailed guidance.
Creating attribute indexes using geoprocessing
The Indexes toolset in the Data Management toolbox provides two primary tools to create and remove attribute indexes.
The Add Attribute Index tool adds a single or multicolumn index to an existing table, feature class, or attribute relationship class. This is available with any ArcGIS license but can only be used with enterprise geodatabases if you have an ArcGIS for Desktop Standard or Advanced license.
The Remove Attribute Index tool removes a single or multicolumn index from an existing table, feature class, or attribute relationship class. This is also available with any ArcGIS license.
Attribute index names
When naming an index in an enterprise geodatabase, it is a good practice to give the index a name that reflects which table or even which column it indexes. However, if the name of the table being indexed changes, your index name may no longer indicate which table is being indexed. Some organizations find it useful to give the index a name that indicates it is an index, such as appending IDX to the beginning or end of the name. For example, an index on a table of addresses might be called ADRS_APK_IDX, where ADRS indicates this index is on the address table, APK denotes the column being indexed, and IDX makes it obvious this is an index.
Similar to table names, the following are true for index names in geodatabases:
- Must be unique in the database
- Must start with a letter
- Cannot contain spaces
- Cannot contain reserved words
ArcGIS imposes a limit of 16 characters on the length of attribute index names. This limit is based on the smallest allowable length within supported databases to facilitate distribution and sharing of data between different geodatabases.
Unique indexes
When you create an index, you are presented with an option of creating the index as unique. Choose this option if the attribute has unique values in each record. This will speed query execution against this attribute, since the database can stop searching after the first matching value is found.
Ascending vs. descending indexes
When you create an index, you are presented with an option of creating the index as ascending or, if the option is not checked, as descending. An ascending index is maintained in ascending order. For example, the city names Athens, Berlin, London, and Paris would appear in that order in an ascending index, whereas in a descending index, they would appear as Paris, London, Berlin, and Athens.
In almost all cases, the direction in which the index is maintained makes little or no difference to the speed of retrieval, since for most queries, indexes are traversed as efficiently forward as they are backward.
Single vs. multicolumn indexes
Indexes can be created for a single column or for multiple columns in a geodatabase. Multicolumn indexes are useful if you frequently specify two or three fields together in a query. In this case, the multicolumn index may provide faster query performance than two or three separate indexes, one on each field.
The order in which fields appear in a multicolumn index is important. In a multicolumn index with column A preceding column B, column A will be used to conduct the initial search. Also, such an index will be much more useful for queries involving column A only than it will be for queries involving column B only.
Deciding whether to create multicolumn or single column indexes or a combination of both involves trade-offs, and the best decision is rarely obvious. Often, though, there is a variety of solutions that can work. For example, if you sometimes query only column A, sometimes only column B, and sometimes both columns, you could choose any of the following approaches:
- Create two separate indexes on A and B.
- Create a multicolumn index on A and B. This index would typically be more efficient for queries involving both columns. For queries involving only A, this index would be slower than an index on A alone. This index would be of little use for queries involving only B. To compensate, you could create an additional index on B.
- Create all three indexes: an index on A, one on B, and a multicolumn index on A and B. This would make sense if all three types of queries are common and the table is queried much more than it is updated.