Spatial indexes and ST_Geometry
Spatial indexes used with ST_Geometry are implemented differently depending on the DBMS used. The IBM Informix Spatial DataBlade module and the ST_Geometry implementation in PostgreSQL use an R-tree index to index the spatial data. ST_Geometry in Oracle and DB2 uses a spatial grid index.
You can create a spatial index in several ways:
- For grid indexes, you can click Add on the Indexes tab of the Feature Class Properties dialog box in ArcCatalog. See Setting spatial indexes.
- Use the sdelayer administration command utility. See the ArcSDE Administration Command Reference.
- Use SQL. See Creating spatial indexes on tables with an ST_Geometry column.
- Use the ArcSDE C or Java application programming interfaces (APIs). See the geodatabase Resource Center.
- Switch a feature class from load-only I/O mode back to normal I/O mode using the sdelayer command with the load_only_io and normal_io operations. ArcSDE automatically drops and re-creates any spatial index created by ArcSDE whenever the feature class is switched from LOAD_ONLY_IO to NORMAL_IO mode.
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
A feature class created using ST_Geometry stroage with a spatial index creates an additional table within the Oracle database. The spatial index table is named S<n>_IDX$, where <n> is the geometry index value for the table. The value can be obtained by querying the SDE.ST_GEOMETRY_COLUMNS 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 Enterprise Manager. The value of <n> represents the LAYER_ID value stored in the LAYERS 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 DBMS 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.
IBM DB2 and Informix
For information on spatial indexes in DB2, see Spatial indexes generated by the DB2 Spatial Extender. For information on spatial indexes in Informix, see Spatial indexes in geodatabases in Informix.