Bottom-Up Building of R-Tree Indexes

When you create an R-tree index, by default, the access method builds the index using a fast bulk-loading algorithm, called bottom-up building. You can set BOTTOM_UP_BUILD='NO' to not use bottom-up building to build the R-tree index.

The algorithm assumes that the four bulk-loading support functions (SFCbits, ObjectLength, SFCvalue, and SetUnion) exist and are defined by the operator class specified in the CREATE INDEX statement. The section Support Functions in Creating a New Operator Class explains what these functions do, if they are supplied by the DataBlade module you are using. For example, the SFCvalue function returns a spatial key, which you can use to sort input data. If the four bulk-loading functions do not exist, the access method builds the R-tree index using a slower algorithm.

You must also have previously created a temporary dbspace for the access method to use bottom-up building when you create an R-tree index. If a temporary dbspace does not exist, or it is too small, then the access method builds the R-tree index using a slower algorithm.

Use the following expression to calculate the minimum size, in bytes, of the temporary dbspace you need to create an R-tree index with a 4-byte spatial key:

numrows * (24 + L)

The numrows variable is the number of rows in the table, and L is the maximum size of the data objects being indexed.

Use the following expression to calculate the minimum size, in bytes, of the temporary dbspace you need to create an R-tree index with an 8-byte spatial key:

numrows * (30 + L)

The default value of the SORT_MEMORY index parameter, specified in R-Tree Index Parameters, is too small for most R-tree indexes. For this reason, you should specify a larger value when you create the index.