The spatial index

Because spatial columns contain multidimensional geographic data, applications that query spatial columns require an index strategy that quickly identifies all geometries that satisfy a specified spatial relationship. To index spatial data, you create a spatial index, called the R-tree index.

The two-dimensional R-tree index differs from the traditional hierarchical (one-dimensional) B-tree index. Spatial data is two-dimensional, so you cannot use the B-tree index for spatial data. Similarly, you cannot use an R-tree index with non-spatial data.

Tip: The loadshp utility automatically creates an R-tree index after you load the data. The ESRI shp2sde command can also create an R-tree index.

To create an R-tree index on the location column of the hazardous_sites table, run the CREATE INDEX statement:

CREATE INDEX location_ix 
   ON hazardous_sites (location ST_Geometry_ops)
   USING RTREE;

The query optimizer does not use the R-tree index unless the statistics on the table are up-to-date. If the R-tree index is created after the data is loaded, the statistics are current and the optimizer uses the index. However, if the index is created before the data is loaded, the optimizer does not use the R-tree index because the statistics are out of date.

If you create the index before you load the data, run the UPDATE STATISTICS SQL statement:

UPDATE STATISTICS FOR TABLE hazardous_sites;

Restriction: You cannot rename a database if the database contains a table that has an R-tree index that is defined on it because R-tree indexes are implemented with secondary access method. Databases that use primary access method (also called virtual table interface) or secondary access method (also called virtual index interface) cannot be renamed.