Extensions of the btree_ops operator class

About this task

Before the database server can support generic B-tree indexes on a UDT, the operator classes associated with the B-tree secondary-access method must be able to handle that data type. The default operator class for the generic B-tree secondary-access method is called btree_ops. Initially, the operator-class functions (strategy and support functions) of the btree_ops operator class handle the built-in data types. When you define a new data type, you must extend these operator-class functions to handle the data type.
Restriction: You cannot extend the btree_ops operator class for the built-in data types.

After you determine how you want to implement the relational operators for a UDT, you can extend the btree_ops operator class so that the query optimizer can consider use of a B-tree index for a query that contains a relational operator.

To extend the default operator class for a generic B-tree index:

Procedure

  1. Write functions for the B-tree strategy functions that accept the UDT in their parameter list.

    The relational-operator functions serve as the strategy functions for the btree_ops operator class. If you have already defined these relational-operator functions for the UDT, the generic B-tree index uses them as its strategy functions. For example, you might have defined the relational-operator functions when you extended an aggregate for the user-defined type. (See Example of extending a built-in aggregate.)

  2. Register the strategy functions in the database with the CREATE FUNCTION statement.

    If you already registered the relational-operator functions, you do not need to reregister them as strategy functions.

  3. Write a function in C or Java™ for the B-tree support function, compare(), that accepts the UDT in its parameter list.
    (The compare() function cannot be in SPL.)

    The compare() function also provides support for a UDT in comparison operations in a SELECT statement (such as the ORDER BY clause or the BETWEEN operator). If you have already defined this comparison function for the UDT, the generic B-tree index uses it as its support function.

    When you define a compare() function, you must also define the greaterthan(), lessthan(), equal() or other functions that use the compare function.

  4. Register the support functions in the database with the CREATE FUNCTION statement.

    For opaque data types, you might have already defined this function to provide support for the comparison operations in a SELECT statement (such as the ORDER BY clause or the BETWEEN operator) on your opaque data type.

What to do next

For more information about strategy functions, see B-tree strategy functions. For information about relational operators for an opaque data type, see Conditional operators for opaque data types.

After you register the support function, use the CREATE INDEX statement to create a B-tree index on the column of the table that contains the UDT. The CREATE INDEX statement does not need the USING clause because you have extended the default operating class for the default index type, a generic B-tree index, to support your UDT.

The query optimizer can now consider use of this generic B-tree index to execute queries efficiently. For more information about the performance aspects of column indexes, see the Informix® Performance Guide.

The previous steps extend the default operator class of the generic B-tree index. You could also define a new operator class to provide another order sequence. For more information, see Creating a new B-tree operator class.