Attribute indexes in the geodatabase

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 a table, feature class, shapefile, or attributed relationship class, you can create attribute indexes for the columns you frequently query. It is recommended that you create indexes on columns that contain unique or almost unique values.

Each index you add slightly slows edits to the feature class or table. Each time you edit a feature class or table containing an attribute index, ArcGIS or the database management system (DBMS) updates the index. Avoid creating an index on a column that is edited more frequently than it is queried or for columns containing few distinct values, as these indexes could be counterproductive for performance. For example, creating an index on a column that contains only the values Yes or No would not improve query performance.

You can create attribute indexes from the Properties dialog box in ArcGIS AllSource or using the Add Attribute Index geoprocessing tool. Once an index is added, you can delete and add it again at any time.

Attribute indexes can be created for single or multiple columns, they can be unique, and for some geodatabases, they can be created in ascending or descending order. This help page provides 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.

Create an attribute index in the Catalog pane

You can create attribute indexes for single or multiple columns in a table, feature class, shapefile, or attributed relationship class by right-clicking its name in the Catalog pane and accessing the Properties dialog box.

Note:

When you open the attribute table in ArcGIS AllSource, columns that are indexed have an asterisk (*) by their name.

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 click Properties.
2. Click the Indexes tab.

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

   Note:

   When you create a table or feature class in ArcGIS, ArcGIS adds a unique index to the object ID column. The index is named FDO_OBJECTID in file geodatabases and mobile geodatabases. In enterprise geodatabases, the index is named r_#_sde_rowid_uk. The index is used by the geodatabase to uniquely identify each feature or row and cannot be removed.

3. To add an attribute index, click Add.
4. For Name, type a 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.

6. From the Fields Available list, click the column for which you want to build this index, and click the arrow to move the column to the Fields selected list.

   To apply the index to multiple columns, repeat this step for each column to be indexed.

7. Optionally, if multiple columns have been selected, use the up and down arrows to change the order of the columns in the index.

   See the Single or multicolumn indexes section below for information about determining what order to use.

8. Click OK to build the index.
9. Click OK to close the Properties dialog box.

Create an attribute index using a geoprocessing tool

The Indexes toolset in the Data Management toolbox provides two attribute index tools: one to create an attribute index and one to remove an attribute index.

The Add Attribute Index tool adds a single or multicolumn index to an existing table, feature class, or attributed relationship class.

The Remove Attribute Index tool removes a single or multicolumn index from a table, feature class, or attributed relationship class.

Attribute index names

When naming an index, it is a good practice to give the index a name that reflects which table or 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 signifies 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 for attribute index names.

Unique indexes

If each attribute value in a column is unique, you can create the index as unique. This decreases the time it takes to query this attribute column, because the database can stop searching after the first matching value is found.

When defined on multiple columns, you can create a unique index if the combination of values across all columns is unique.

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

Ascending or descending indexes

When you create an index, you can create the index as ascending or 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, because for most queries, indexes are traversed as efficiently forward as they are backward.

Single or 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 columns together in a query. In this case, the multicolumn index may provide faster query performance than two or three separate indexes, one on each column.

The order in which columns 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, as illustrated in the following 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.