The guidelines provided in this topic can help improve the performance of spatial queries when you have to manually calculate the size of a spatial grid index in Oracle and DB2.
Grid cell size impacts the size of the spatial index table. Setting up the spatial index means balancing the cell sizes—smaller cell sizes mean more cells per shape, which require more entries in the spatial index table.
If you are using third-party client applications, the applications and spatial data profiles can vary from one system to another. In these cases, you may need to experiment with the spatial index, trying different cell sizes and different grid-level configurations.
- Consider how many grid levels are needed and remember that the database optimizer scans the spatial index table once per grid level. Often, a single grid level is the best solution for a feature class despite the notion of distributing geometries evenly across many grid levels to minimize the spatial index entries.
- Use one grid level for pure point type feature classes and consider making the cell sizes large. Spatial queries generally process point geometries faster than other geometry types.
- Monitor the spatial index. Tuning a spatial index is difficult if the data changes frequently. Tuning depends on the structure of the spatial data. Periodically assess the spatial index as your spatial data changes.
- Base the spatial index on the application. Match the spatial index grid cell sizes to the extent of the application window. By doing so, the application is probably viewing exact entries in the spatial index table. This helps size the spatial index table suitably and reduces the amount of processing, because fewer candidate feature IDs must be evaluated against the feature table.
- For unknown or variable application windows, start by defining one grid level with a cell size three times the average feature extent size. Query the business table to obtain the average feature size. The following SQL statement gets the average feature size for a feature service in Oracle:(where <N> is the spatial table and shape is the <N> table's geometry column.) Such a spatial index configuration minimizes the number of rows in the spatial index table while maintaining the proficiency of the index, because the majority of the features can be referenced by less than one or two grid cells.
SELECT (AVG(SDE.ST_MAXX(shape) – SDE.ST_MINX(shape)) + AVG(SDE.ST_MAXY(shape) – SDE.ST_MINY(shape))) / 2 from <N>; - Design the spatial table around spatial data categories such as type, geometry size, and distribution. Sometimes, a carefully designed feature class, using these categories, can substantially boost the performance of spatial queries.
If you create your data through ArcGIS for Desktop, the spatial grid index is calculated for you.
For more information about the DB2 Spatial Extender grid index, see the IBM DB2 Spatial Extender and Geodetic Extender User's Guide and Reference.