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.

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.

NOTE: 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 disabled.

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.

The Indexes toolset in the Data Management toolbox provides two primary tools to create and remove attribute indexes.

Add Attribute Index adds a single or multicolumn index to an existing table, feature class, or attribute relationship class. This is available with any ArcGIS license.

Remove Attribute Index removes a single or multicolumn index from an existing table, feature class, or attribute relationship class. This is also available with any ArcGIS license.

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, 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.

NOTE: 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 disabled 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.

NOTE: 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.

NOTE: 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.