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 topic Support functions 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.