Once data has been loaded into the spatial tables through either bulk or transactional loading, a spatial index (that is, a spatial R-tree index) must be created on each geometry column in the tables for efficient access to the data. For example, the following statement creates a spatial index named territory_idx using default values for all parameters:
CREATE INDEX territory_idx ON territories (territory_geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
For detailed information about options for creating a spatial index, see the documentation for the CREATE INDEX statement in SQL Statements for Indexing Spatial Data.
If the index creation does not complete for any reason, the index is invalid and must be deleted with the DROP INDEX <index_name> [FORCE] statement.
Within each geometry column to be indexed, all the geometries must have the same SDO_SRID value.
Spatial indexes can be built on two, three, or four dimensions of data. The default number of dimensions is two, but if the data has more than two dimensions, you can use the sdo_indx_dims parameter keyword to specify the number of dimensions on which to build the index. (For information about support for three-dimensional geometries, see Three-Dimensional Spatial Objects. For an explanation of support for various combinations of dimensionality in query elements, see Data and Index Dimensionality_ and Spatial Queries.)
If you are not using the automatic undo management feature or the PGA memory management feature, or both, of Oracle Database, see Rollback Segments and Sort Area Size for information about initialization parameter values that you may need to set. Both automatic undo management and PGA memory management are enabled by default, and their use is highly recommended.
The tablespace specified with the tablespace keyword in the CREATE INDEX statement (or the default tablespace if the tablespace keyword is not specified) is used to hold both the index data table and some transient tables that are created for internal computations. If you specify WORK_TABLESPACE as the tablespace, the transient tables are stored in the work tablespace.
For large tables (over 1 million rows), a temporary tablespace may be needed to perform internal sorting operations. The recommended size for this temporary tablespace is 100*n bytes, where n is the number of rows in the table, up to a maximum requirement of 1 gigabyte of temporary tablespace.
To estimate the space that will be needed to create a spatial index, use the SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE function, described in SDO_TUNE Package (Tuning).
This section also contains the following topics: