The following are tips on using the spatial index grid that is used by Oracle and DB2 on tables containing an ST_Geometry column:
- Symptoms when a grid size is wrong
If a grid size is determined to be too large or too small when creating a spatial index in Oracle, the following error occurs:
CREATE INDEX farm_idx on farm(shape) INDEXTYPE is SDE.ST_SPATIAL_INDEX PARAMETERS ('st_grids=1000,3000,9000 st_srid=31'); \CREATE INDEX farm_idx on farm(shape) * ERROR at line 1: ORA-20006: InValid Grid Size ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 43 ORA-06512: at "SDE.ST_POLYGON", line 236
To identify common issues with DB2, please see chapter 15—Identifying Problems in the DB2 Spatial Extender and Geodetic Extender User's Guide and Reference. When using Informix, see the Error Messages section of the Informix Spatial DataBlade Module User's Guide.
- Deferring creation of a spatial index after loading data
Loading or appending data into a spatial table can decrease the total time it takes to complete the load. It also results in less fragmentation of column indexes, which improves query performance. Therefore, it can be useful to defer creating the spatial index until you complete loading your data into the spatial table.
- Using ST_AsText to display the ST_Geometry contents
The human-readable well-known text representation is useful when you want to examine the content of individual geometries. Use the ST_AsText function in a SELECT statement to translate the ST_Geometry contents into well-known text for display.