Bottom-up versus top-down index builds

R-tree indexes are created using one of two options: top-down build or bottom-up build. When an index is built from the top down, spatial objects are inserted into the R-tree one at a time. Objects are grouped together in the R-tree according to their spatial proximity to one another.

When an index is built from the bottom up, objects are first sorted according to a numeric value which is generated by the SE_SpatialKey() function. Then the R-tree is built by inserting all objects into the leaf pages (the bottom level) of the tree in sorted order and then building a hierarchy of bounding boxes above the leaf pages.

Size (in bytes) = Number of rows in table * 1000

Refer to your HCL OneDB™ Administrator's Guide for more information about dbspaces.

The NO_SORT option eliminates the need for a temporary dbspace, but your spatial data must be presorted in the table. This means that it must be inserted into the table in sorted order, or sorted by means of a clustered B-tree index after it is loaded.

You should also note that the bottom-up build option only makes sense if you load all of your spatial data into a table before you create an R-tree index. There is no performance advantage to either build option if you create an index on an empty table and then insert data.

Depending on certain characteristics of your spatial data, an index built from the top down may be more effective than one built from the bottom up. Properties that can adversely affect a bottom-up built index include many objects that overlap each other, or many objects that are very close together, such that the sort key produced by the SE_SpatialKey() function is the same value for a large number of objects.