Using the NO_SORT index parameter

About this task

If the DataBlade® module that you are using provides a function that returns a numeric spatial key given an object of the data type that is being indexed, you can use this function to create a statically clustered table according to a functional B-tree index. Then, when you create an R-tree index on the resulting clustered table, the R-tree secondary access method does not need to sort the data as it builds an index from the bottom up, because the table is already sorted according to the same criterion that the R-tree bottom-up build would use.

To first sort a table and then create an R-tree index using the NO_SORT index parameter:

Procedure

  1. Check your DataBlade module documentation for a function that returns a spatial key given an object of the data type that is being indexed.

    For this procedure, assume this function is called SpatKey().

  2. Create a clustered functional B-tree index on your table using the SpatKey() function
    For example, 
    CREATE CLUSTER INDEX btree_func_index on 
    table1 (SpatKey(column1));

    In the example, btree_func_index is the name of the clustered functional B-tree index, table1 is the name of the table, and column1 is the name of the column that contains the spatial data.

  3. Create the R-tree index on the spatial_column_name column, specifying the NO_SORT='YES' index parameter:
    CREATE INDEX rtree_index ON table1 (column1 my_ops) 
    USING RTREE (NO_SORT = 'YES');

    In the example, rtree_index is the name of the R-tree index and my_ops is the name of the operator class associated with the data type of column column1.

  4. Because the R-tree index does not use the clustered functional B-tree index, you can drop the B-tree index if you want:
    DROP INDEX btree_func_index;