Syntax for creating an R-tree index

To use the R-tree access method, you create an index on a column of a spatial type.

Syntax


Element	Description
`column_name`	The name of the spatial column.
`index_name`	The name to give your index.
`index_options`	The index options are FRAGMENT BY and IN.
`parameters`	The parameters available for R-tree indexes are bottom_up_build, BOUNDING_BOX_INDEX, NO_SORT, sort_memory, and fill_factor.
`table_name`	The name of the table that contains the spatial column to index.

Restriction: You cannot rename databases that contain R-tree indexes.

The BOTTOM_UP_BUILD, BOUNDING_BOX_INDEX, and NO_SORT parameters affect the size of the index and the speed at which it is built. The following table shows the valid combinations of these parameters.

Table 1. Parameters of the CREATE INDEX statement for spatial data
Parameters clause of CREATE INDEX statement	Description
BOTTOM_UP_BUILD='no', BOUNDING_BOX_INDEX='no', NO_SORT='no'	Creates an index by inserting spatial objects into the R-tree one at a time A copy of each object's in-row data is stored at the leaf level of the R-tree. This is the default if the DBSPACETEMP parameter in your onconfig file is not defined.
BOTTOM_UP_BUILD='no', BOUNDING_BOX_INDEX='yes', NO_SORT='no'	Creates a more compact index from the top down Only the bounding boxes of each object are stored at the leaf level of the R-tree. No temporary dbspace is required.
BOTTOM_UP_BUILD='yes', BOUNDING_BOX_INDEX='no', NO_SORT='no'	Creates an index by sorting the spatial data and then building the R-tree from the bottom up This is generally faster than building an index from the top down. This is the default if you have a temporary dbspace and it is specified by the DBSPACETEMP parameter in your onconfig file.
BOTTOM_UP_BUILD='yes', BOUNDING_BOX_INDEX='no', NO_SORT='yes'	Creates an index in less time Does not require a temporary dbspace Spatial data must be presorted, either by loading the data in a predetermined order or by creating a clustered functional B-tree index by using the SE_SpatialKey() function.
BOTTOM_UP_BUILD='yes', BOUNDING_BOX_INDEX='yes', NO_SORT='no'	Creates a more compact index from the bottom up, which is faster than building from the top down This is the default if you have a temporary dbspace and it is specified by the DBSPACETEMP parameter in your onconfig file. Spatial data need not be presorted.
BOTTOM_UP_BUILD='yes', BOUNDING_BOX_INDEX='yes', NO_SORT='yes'	Creates a more compact index in less time Does not require a temporary dbspace Spatial data must be presorted.