Spatial indexes used with ST_Geometry are implemented differently depending on the database management system used. ST_Geometry in Oracle and IBM Db2 uses a spatial grid index. The IBM Informix Spatial DataBlade module, the ST_Geometry implementation in PostgreSQL, and SQLite use an R-tree index to index the spatial data. In SQLite, the R-tree index is a virtual table.
You can create a spatial index in the following ways:
- For grid indexes, you can click Add on the Indexes tab of the Feature Class Properties dialog box in ArcCatalog. See Rebuild a spatial index.
- Use SQL.
Be aware that when you issue spatial relationship queries against tables that contain ST_Geometry columns, they utilize a spatial index to speed the query process only when you execute certain spatial relationship functions. These are listed in When are spatial indexes used?.
Oracle
Feature classes that use ST_Geometry storage use a separate table to store information for the spatial index. The spatial index table is named S<n>_IDX$, where <n> is the geometry index value for the table. The geometry index value is stored in the SDE.ST_GEOMETRY_COLUMNS geodatabase system table. The spatial index table is created as an Oracle Indexed Organized Table (IOT). The spatial index on the ST_Geometry attribute appears as A<n>_IX1 when viewed through Oracle Enterprise Manager. The value of <n> represents the LAYER_ID value stored in the LAYERS geodatabase system table.
Two additional indexes are created on the S<n>_IDX$ table: S<n>$_IX1 and S<n>$_IX2. You can specify how these indexes are stored in the database management system by altering the S_STORAGE parameter in the DBTUNE configuration keyword you specify when creating a feature class.
If you create partitioned business tables that contain an ST_Geometry column, you may also want the spatial index to be partitioned. There are two types of partitioning methods: global and local. By default, global partitioned indexes are created on partitioned business tables. To create a local partitioned index, you must add the keyword LOCAL to the end of the CREATE INDEX statement. To enable ArcGIS to add LOCAL to the end of the CREATE INDEX statement for the spatial index, set the parameter ST_INDEX_PARTITION_LOCAL to TRUE under the DEFAULTS keyword.
PostgreSQL
In PostgreSQL, the R-tree index is implemented using the Generalized Search Tree (GiST) index infrastructure. For information on GiST indexing, see the PostgreSQL documentation.
SQLite
The spatial index in SQLite is a set of tables used as an R-tree index.
Db2 and Informix
For information on spatial indexes in Db2 and Informix, see the IBM documentation.