NOTE: This topic deals with spatial grid indexes. Spatial grid indexes are used with tables using binary geometry in Oracle and SQL Server and tables using ST_Geometry in Oracle. IBM DB2 Spatial Extender also uses a multilevel grid spatial index, but since the spatial index in DB2 is generated by the Spatial Extender, you should use the Index Advisor tool provided by IBM to help tune this index. You can read more about this tool at IBM's Information Center.

The multilevel spatial index is used to decrease the time it takes to locate features that match a spatial query. It defines imaginary x/y grids for a feature class. There may be one, two, or three of these grid levels defined per feature class. The size of each grid in an ArcSDE feature class must be at least three times the previous grid size. Most feature classes need only one grid level, but more levels may be needed if the average sizes of the feature envelopes (the bounding boxes of each feature) vary widely. For more information on spatial indexes, see An overview of spatial indexes in the geodatabase in the "Defining the properties of a geodatabase" section of the help.

Each feature in the feature class is indexed using only one of the grid levels; small features are indexed in the first level and larger features in the second or third grid levels, if these levels are present. ArcSDE places an entry (a row) in the spatial index table for every instance in which a single feature intersects a single cell in the one grid level used for that feature.

When spatial data is displayed or previewed in ArcGIS, ArcSDE performs filtering to return only those features that fall within the envelope of the display extent.

• ArcSDE identifies which grid cells and levels intersect the minimum bounding rectangle of the display extent.
• ArcSDE returns all the features within those grid cells that intersect the minimum bounding rectangle. This is the results set.

When you perform a query that includes a relational operator, such as finding the intersection of geometries, comparisons are done to reduce the result set to the features that satisfy the relational operator in the query.

ArcGIS 9.2 and later releases

Beginning with ArcGIS 9.2, the spatial index is calculated for you when you first load data into a dataset and provide a coordinate system in ArcGIS Desktop. You should not need to tune such spatial grid indexes. However, you can recalculate the spatial index if you want. This is done in ArcCatalog.

1. In ArcCatalog, right-click the feature class and click Properties.
2. Click the Indexes tab.
3. The bottom section of this tab contains the spatial index information. You can click Recalculate to rebuild the spatial index.

NOTE: If the Recalculate button is not active, you are likely using a geometry storage type that does not use a spatial grid index or you are not the owner of the dataset or an administrator in the database.

ArcGIS 9.1 and earlier releases

If you are using an older release of ArcGIS and ArcSDE (9.1 or lower) and are using spatial grid indexes, you likely defined the spatial index yourself or used the default grid size settings. The default grid size settings are designed to ensure that the data can be loaded. In most circumstances, the default grid sizes will be appropriate for fast spatial queries. However, depending on the characteristics of your data, they may not be the optimal size. Tuning the grid sizes might result in better spatial query performance.

When you tune the spatial index, you must balance selectivity against reducing the number of entries in the spatial index. The greater the number of entries in the spatial index, the fewer features there will be in the result sets, which the secondary spatial filter must examine and possibly eliminate to meet the requirements of the spatial query. However, the more spatial index entries there are, the larger the spatial index will be, which will slow down queries on the spatial index table.

When performance tuning, the only way to tell if you have made a positive change is to monitor the results of each change. ArcSDE provides statistics about the spatial index which, along with performance testing, can ease the tuning process. The administration command sdelayer –o si_stats is the primary tool for reporting spatial index grid statistics used to tune the spatial index.

If you feel you need to tune your spatial index, you can follow the steps below.

NOTE: Building a new spatial index for an ArcSDE feature class is a server-intensive operation; you should not do it for very large feature classes when a large number of users are logged in to the server.

NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only

The general steps to follow when tuning a spatial index are as follows:

1. Establish a repeatable test to measure spatial query performance. This may be a manual test or you might create an automated test that performs and times a defined set of spatial queries.
2. Use sdelayer –o si_stats to gather beginning spatial index statistics. Be sure to record the original size of the grid levels in case you decide to revert to them. You might decide to do this if you find the changes you made to the grid size cause spatial query performance to decline.
The syntax for sdelayer –o si_stats is as follows:

sdelayer –o si_stats –l <business_table,spatial_column> 
[–i <service>] [–s <server_name>] [–D <database>]
–u <DB_user_name> –p <DB_user_password> [–q]




NOTE: If you are not the owner of the table/feature class, you must qualify the table name with the owner name, for example, owner.table.

The following is an example of what gets returned by sdelayer –o si_stats:

Layer Administration Utility

Layer 5 Spatial Index Statistics:
Level 1, Grid Size 4.940464025

 Grid Records: 966                                                       
 Feature Records: 23                                                    
 Grids/Feature Ratio: 42                                                
 Avg. Features per Grid: 16.37                                       
 Max. Features per Grid: 96                                           
 % of Features Wholly Inside 1 Grid: 59.71                     

                   Spatial Index Record Count By Group          
Grids:        <=4    >4    >10    >25    >50   >100    >250   >500

Features:      23     0      0      0     0      0      0      0        
% Total:      100%    0%     0%     0%    0%     0%     0%     0%



The statistic Grids/Feature Ratio shows the ratio of the number of entries in the spatial index table versus the number of features in the feature class. Fewer entries in the spatial index table equate to faster queries. Optimally, the Grids/Feature Ratio should be less than 2. If it exceeds 4, consider modifying the spatial index settings. As you can see in the example above, with a Grids/Feature Ratio of 42, this spatial index needs modifying.
At the end of the output is a histogram showing how many spatial index records (entries) exist for each feature. The majority of the features should have fewer than four records. Even in a well-tuned spatial index, if there are a few features that are significantly larger than the others, these features will have more spatial index entries. If many features have more than four spatial index entries, consider modifying the spatial index.
3. Change the spatial index grid settings. You can use ArcCatalog or the sdelayer command to do this. For more information, see the topic Setting spatial indexes in the "Defining the properties of a geodatabase" section of the help.
To use sdelayer, do the following:
• Use sdelayer –o load_only_io to drop the spatial index. Switching the feature class to load-only mode drops the spatial index.

sdelayer –o load_only_io –l <table,column> 
[–i <service>] [–s <server_name>] [–D <database>]
[–u <DB_user_name>] [–p <DB_user_password>] [–q]



• Use sdelayer –o alter –g n,n,n to specify new grid sizes. Specify 0 for the second or third grid size if not used.

sdelayer –o alter –l <table,column> –g {<grid_sz1>[,<grid_sz2>[,<grid_sz3>]]}
[–S <layer_description_str>] [–i <service>] [–s <server_name>]
[–D <database>] –u <DB_User_name> [–p <DB_User_password>] [–N] [–q]



• Use sdelayer –o normal_io to rebuild the spatial index and make the layer accessible again.

sdelayer –o normal_io –l <table,column>
[–i <service>] [–s <server_name>] [–D <database>]
[–u <DB_user_name>] [–p <DB_user_password>] [–q]




4. Run your query performance test again, and check the spatial index statistics to see if the changes had the desired effect. If not, you could try again or you might choose to undo the changes, especially if they have a negative effect on performance. You would do this by repeating step 3 using the grid index sizes you recorded in step 2.
5. Repeat steps 3 and 4 until you run out of reasonable changes to make or you find that remaining changes have a negligible effect on performance.

Tips A good starting place is to set the grid size to three times the length of the edge of an average-sized feature. Where possible, use only one spatial index grid level. Because it needs to search each level used in each feature class, ArcSDE usually performs best when a single spatial index grid level is used. However, feature classes with highly variable feature envelope sizes may benefit from multilevel spatial indexes. You may choose to experiment with a multilevel spatial index to see if it will improve the spatial index statistics and the query performance. Where possible, specify a grid size where a high percentage of features fall wholly within one grid cell. If the percentage falls below 80 percent, consider modifying the spatial index settings. For details on the use of sdelayer and other administration commands, consult the Administration Command Reference that is provided with the software. You can also download a PDF version of the file from the ESRI Resource Center.