Create Spatial Index Notes

If the index creation does not complete for any reason, the index is invalid and must
be deleted with the DROP INDEX [FORCE] statement.

R-tree 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. However, if a spatial index has been built on more than two dimensions of a layer, the only spatial operator that can be used against that layer is SDO_FILTER (the primary filter or index-only query), which considers all dimensions. The SDO_RELATE, SDO_NN, and SDO_WITHIN_DISTANCE operators are disabled if the index has been built on more than two dimensions.

Rollback Segment
The rollback segment should be 100*n bytes, where n is the number of rows of data to be indexed.

SORT_AREA_SIZE
The optimal value depends on the database size, but a good guideline is to make it at least 1 million bytes when you create an R-tree index. For example, to change the value to 20 million bytes: ALTER SESSION SET SORT_AREA_SIZE = 20000000;

Tablespace
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.

• The R-tree index data table requires approximately 70*n bytes (where n is the number of rows in the table).
• The transient tables require up to approximately 200*n bytes (where n is the number of rows in the table); however, this space is freed up after the R-tree index is created.

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.