Examples of creating R-tree indexes

The following example shows how to create a table called circle_tab that contains a column of data type MyCircle and an R-tree index called circle_tab_index on the circles column:

CREATE TABLE circle_tab
(
    id        INTEGER,
    circles   MyCircle
);

CREATE INDEX circle_tab_index
ON circle_tab ( circles MyShape_ops )
USING RTREE;

The following example shows how to create a similar R-tree index that is stored in the dbsp1 dbspace instead of the dbspace in which the circle_tab table is stored:

CREATE INDEX circle_tab_index2
ON circle_tab ( circles MyShape_ops )
USING RTREE
IN dbsp1;

The following example shows how to create a fragmented R-tree index on the circle_tab table:

CREATE INDEX circle_tab_index3
ON circle_tab ( circles MyShape_ops )
USING RTREE
FRAGMENT BY EXPRESSION
id < 100 IN dbsp1,
id >= 100 IN dbsp2;

All shapes with id less than 100 are stored in the dbsp1 dbspace, and the remainder are stored in the dbsp2 dbspace.

The following example shows how to create a fragmented table called circle_tab_frag and then an R-tree index on the table called circle_tab_index4:

CREATE TABLE circle_tab_frag
(
    id        INTEGER,
    circles   MyCircle
)
FRAGMENT BY EXPRESSION
id < 100 IN dbsp1,
id >= 100 IN dbsp2;

CREATE INDEX circle_tab_index4
ON circle_tab_frag ( circles MyShape_ops )
USING RTREE;

All shapes with id less than 100 are stored in the dbsp1 dbspace, and the remainder are stored in the dbsp2 dbspace.

The following example shows how to create a fragmented table called circle_tab_frag and then an R-tree index on the table called circle_tab_index4:

CREATE TABLE circle_tab_frag
(
    id        INTEGER,
    circles   MyCircle
)
FRAGMENT BY EXPRESSION
id < 100 IN dbsp1,
id >= 100 IN dbsp2;

CREATE INDEX circle_tab_index4
ON circle_tab_frag ( circles MyShape_ops )
USING RTREE;

Although the R-tree index is not explicitly created with fragmentation, it is fragmented by default because the table it is indexing, circle_tab_frag, is fragmented.

The following example shows how to specify index parameters when you create an R-tree index:

CREATE INDEX circle_tab_index5
ON circle_tab ( circles MyShape_ops )
USING RTREE (BOTTOM_UP_BUILD='YES', FILLFACTOR='80', SORT_MEMORY='320');

The parameters specify that the R-tree index should be built using fast bulk loading, that the fillfactor is 80, and that the R-tree access method has 320 KB of shared memory available for sorting.

The following example shows how to drop an R-tree index:

DROP INDEX circle_tab_index;