Functional R-tree indexes

You can also use the R-tree access method to create a functional R-tree index. A functional index supports retrieval of table rows according to the value of some computation done on the columns of the rows. The value is not actually stored in the table, but it is precomputed and used to build an index.

To create a functional R-tree index, the return type of the function must be a data type that is compatible with an R-tree index.

You cannot build a functional R-tree index with a function that specifies an opaque data type that contains a reference to a smart large object as a return type. This is true for all functional indexes, not just R-tree functional indexes.

Functional R-tree indexes are not bounding-box-only indexes; they store the data objects themselves in leaf pages.

The examples in the rest of this topic show how to create and use a functional R-tree index on a table that stores point coordinates. Although the table does not contain any columns of a data type that can be indexed by an R-tree index, the functional R-tree index allows you to use the R-tree access method to search for specific points in the table.

The following example shows how to create and populate a coordinates table that has two columns that store the point coordinates; the x column stores x-coordinates and the y column stores y-coordinates:

CREATE TABLE coordinates
(
    id INTEGER,
    x  FLOAT,
    y  FLOAT
);    

INSERT INTO coordinates VALUES (1, 2.0, 3.0 );
INSERT INTO coordinates VALUES (2, 4.0, 5.0 );

The following example shows how to create a functional R-tree index called coordinates_idx on the two coordinate columns of the coordinates table using the MyPoint() function:

CREATE INDEX coordinates_idx
ON coordinates (MyPoint (x,y) MyShape_ops)
USING RTREE;

The following example shows a query that could use the coordinates_idx functional R-tree index:

SELECT id FROM coordinates
WHERE MyPoint(x,y) = 'point(2.0, 3.0)';

The query searches for all points in the coordinates table that have the coordinates (2.0, 3.0).

For more information about how to create functional indexes, refer to the Informix® Guide to SQL: Syntax.