Optimizing Geodatabases - Learning ArcGIS Geodatabases - page 114

Using indexes effectively
Although indexing is a great tool for optimization, it can be harmful if implemented incor-
rectly. When you index a column, the geodatabase creates an additional hidden structure
that needs to be managed and refreshed frequently. The more indexes you have, the more
extra work the geodatabase has to endure to update those indexes. Indexes also slow down
update operations such as INSERT , UPDATE , and DELETE , because the geodatabase has
to change the indexes as well. More indexes mean that geodatabases need to update and,
perhaps, even recreate those indexes. Avoid creating indexes on columns with very few dis-
tinct values such as rating and category because they often won't give you the performance
you desire. It is good to create indexes on unique columns or nearly unique
columns—indexes thrive on uniqueness and will always boost the performance of your
geodatabase. You can calculate the percentage of indexing performance using the following
formula:
In the preceding formula, a is the attribute to be indexed and ind(a) is the percentage of in-
dexing efficiency; 100 percent being the maximum and 0 percent being the lowest. d(a) is
the number of distinct values in the attribute column a and n(a) is the number of total val-
ues in a . Note that if a is a primary key, then ind(a) is 100 percent. This also explains why
the RATING and CATEGORY fields score low on indexing performance in this formula.