Creating attribute indexes

License:

Adding or modifying attribute indexes in ArcSDE geodatabases is not available in ArcView.

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. Attribute indexes can be created by accessing the Properties dialog box in ArcCatalog or with geoprocessing. 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 an ArcSDE geodatabase, refer to your DBMS documentation for more detailed guidance.

Creating attribute indexes in ArcCatalog

Attribute indexes can be created for single or multiple fields in a geodatabase feature class or table by right-clicking the table name in the Catalog tree and accessing the Properties dialog box.

Steps:

1. In the Catalog tree, right-click the table or feature class for which you want to create an index and click Properties.
2. Click the Indexes tab.
3. Click Add.
4. Type the name for the new index.
5. Check the Unique check box if your field values are unique. Check the Ascending check box to create an ascending index.

   The Unique and Ascending settings are not used in file geodatabases and can be left unchecked. The Ascending setting is not used in Oracle ArcSDE geodatabases. The Unique and Ascending settings are not available for SQL Server ArcSDE geodatabases and are unavailable.

6. Click the field or fields for which you want to build this index.
   Note:

   Multicolumn indexes are not supported in file geodatabases.

7. Click the arrow button to move the fields to the Fields selected list.
8. Use the up and down arrows to change the order of the fields in the index.
9. Click OK.
10. Click Apply to build the index or click OK to build the index and close the Properties dialog box.

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.

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 ArcSDE 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 ArcSDE geodatabases:

• Must be unique in the database
• Must start with a letter
• Cannot contain spaces
• Cannot contain reserved words

There are no restrictions on how you can name an attribute index in a file geodatabase. Index names in personal geodatabases cannot contain spaces or 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 in order to facilitate distribution and sharing of data between different geodatabases.

Unique indexes

The Unique option is not used in file geodatabases and can be left unchecked. The Unique option is not available for SQL Server ArcSDE geodatabases and is unavailable on the Add Attribute Index dialog box.

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.

Note, however, that you cannot edit a feature class that has a unique index on a user-defined field in a personal geodatabase. Also, you cannot edit a feature class that is in a feature dataset with another feature class with a unique index on a user-defined field. When attempting to start editing a personal geodatabase, ArcMap returns this error:

"Could not edit any of the map's layers. Check to see if a layer or table contains a unique index on a user managed column."

If you have a unique index on a field in a personal geodatabase and need to edit, use ArcCatalog to drop the unique index and re-create it as a nonunique index.

Ascending vs. descending indexes

The Ascending option is not used in file or Oracle ArcSDE geodatabases and can be left unchecked. The Ascending option is not available for SQL Server ArcSDE geodatabases and is disabled on the Add Attribute Index dialog box.

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

File geodatabases do not support multicolumn indexes. The Feature Class and Table Properties dialog boxes do not allow you to specify a multicolumn index. The Add Attribute Index GP tool and ArcObjects allow you to specify a multicolumn index, and although the index you create appears to be a multicolumn index when viewed from the Feature Class or Table Properties dialog box in ArcCatalog, it's actually a separate index on each field.

Indexes can be created for a single column or for multiple columns in a personal or ArcSDE 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 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.