Creating a new B-tree operator class

About this task

To traverse the index structure, the generic B-tree index uses the sequence that the relational operators define. By default, a B-tree uses the lexicographical sequence of data because the default operator class, btree_ops, contains the relational-operator functions. (For more information about this sequence, see Change the sort order.) For a generic B-tree to use a different sequence for its index values, you can create a new operator class for the btree secondary-access method. You can then specify the new operator class when you define an index on that data type.

When you create a new operator class for the generic B-tree index, you provide an additional sequence for organizing data in a B-tree. When you create the B-tree index, you can specify the sequence that you want a column (or user-defined function) in the index to have.

To create a new operator class for a generic B-tree index:

Procedure

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

    The B-tree secondary-access method expects five strategy functions; therefore, any new operator class must define exactly five. The parameter data types can be built in or user defined. However, each function must return a Boolean value. For more information about strategy functions, see B-tree strategy functions.

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

    You must register the set of strategy functions for each data type on which you are supporting the operator class.

  3. Write the external function for the new B-tree support function that accepts the appropriate data type in its parameter list.

    The B-tree secondary-access method expects one support function; therefore, any new operator class must define only one. The parameter data types can be built-in or UDTs. However, the return type must be integer. For more information about support functions, see B-tree support function.

  4. Register the new support function in the database with the CREATE FUNCTION statement.

    You must register a support function for each data type on which you are supporting the operator class.

  5. Create the new operator class for the B-tree secondary-access method, btree.
    When you create an operator class, specify the following in the CREATE OPCLASS statement:
    • After the OPCLASS keyword, the name of the new operator class
    • In the FOR clause, btree as the name of the secondary-access method with which to associate the operator class
    • In the STRATEGIES clause, a parenthetical list of the names of the strategy functions for the operator class

      You registered these functions in step 2. You must list the functions in the order that the B-tree secondary-access method expects: the first function is the replacement for lessthan(), the second for lessthanorequal(), and so on.

    • In the SUPPORT clause, the name of the support function to use to search the index

      You registered this function in step 4. It is the replacement for the compare() function.

    For more information about how to use the CREATE OPCLASS statement, refer to the Informix® Guide to SQL: Syntax.

Results

These steps create the new operator class of the generic B-tree index. You can also extend the default operator class to provide support for new data types. For more information, see Extensions of the btree_ops operator class.

To use the new operator class, specify the name of the operator class after the column or function name in the CREATE INDEX statement.