Attribute indexes in the geodatabase

Available with Standard or Advanced license.

ArcGIS uses indexes to quickly locate data. Attribute indexes are used to locate records that match an attribute query, and spatial indexes are used to locate features that match a spatial query.

Attribute indexes can speed up joins and other attribute queries on tables, feature classes, shapefiles or attributed relationship classes. 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 an existing table, feature class, shapefile or attributed relationship class, you can create attribute indexes for the fields you frequently query against. Ideally, you want to create indexes on columns with unique or almost unique values. Create only those indexes you really need, since each index you add slightly slows edits to the feature class. Each time you edit a feature class containing an attribute index, ArcGIS or the DBMS will perform maintenance on the index. Avoid creating an index on a field that is frequently edited or for fields containing very few distinct values, such as Yes or No, as these indexes could be counterproductive for performance.

Attribute indexes can be created by accessing the Properties dialog box in ArcGIS Pro or with the Add Attribute Index geoprocessing tool. 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 enterprise geodatabase, refer to your DBMS documentation for more detailed guidance.

Creating attribute indexes in ArcGIS Pro

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

1. In the Catalog pane, right-click the table, feature class, shapefile or attributed relationship class for which you want to create an index and select Properties.
2. Select the Indexes tab.

   The Attribute Index section displays the existing indexes for this dataset.

   Note:

   The FDO_OBJECTID index is created and used by the geodatabase to uniquely identify each feature and cannot be removed. If you click on the FDO_OBJECTID, the Fields section displays the field for which this index was created for.

3. To add a new attribute index, click Add.
4. For Name, type the name for the new index.
5. Optionally, 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 or SQL Server.

6. From the Fields Available list, 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 right arrow button to move the field or fields to the Fields selected list.
8. Optionally, if multiple fields have been selected, use the up and down arrows to change the order of the fields in the index.
9. Click OK to build the index.
10. Click OK to close the Properties dialog box.

Creating attribute indexes using geoprocessing

The Indexes toolset in the Data Management Tools 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 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

There are no restrictions on how you can name an attribute index in a file geodatabase.

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.

Note:

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.

Note:

The Unique setting is not used in file geodatabases and can be left unchecked.

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, city name values of 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:

The Ascending setting is not used in Oracle, SQL Server, or file geodatabases, and can be left unchecked.

Single vs. multicolumn indexes

File geodatabases do not support multicolumn indexes. The Properties dialog box does not allow you to specify a multicolumn index. The Add Attribute Index tool does allow you to specify a multicolumn index, and although the index you create appears to be a multicolumn index when viewed from the Properties dialog box in the Catalog pane, it's actually a separate index on each field.

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.