Reasons for Extending btree_ops

The strategy functions of btree_ops are the relational operations that end users can use in expressions. (For a list of the relational operators, see B-Tree Strategy Functions.) The generic B-tree index handles only the built-in data types. When you write relational-operator functions that handle a new UDT, you extend the generic B-tree so that it can handle the UDT in a column or a user-defined function. To create B-tree indexes on columns or functions of the new data type, you must write new relational-operator functions that can handle the new data type.

In the relational-operator functions, you determine the following behavior of a B-tree index:

• What single value does the B-tree secondary-access method use to order the index?

  For a particular UDT, the relational-operator functions must compare two values of this data type for the data type to be stored in the B-tree index.

• In what order does the B-tree index sort the values?

  For a particular UDT, the relational-operator functions must determine what constitutes an ordered sequence of the values.

Generating a Single Value for a New Data Type

A B-tree index indexes one-dimensional objects. It uses the relational-operator functions to compare two one-dimensional values. It then uses the relationship between these values to determine how to traverse the B-tree and in which node to store a value.

The relational-operator functions handle built-in data types. (For more information on built-in data types, see the chapter on data types in the IBM Informix: Guide to SQL Reference.) The built-in data types contain one-dimensional values. For example, the INTEGER data type holds a single integer value. The CHAR data type holds a single character string. The DATE data type holds a single date value. The values of all these data types can be ordered linearly (in one dimension). The relational-operator functions can compare these values to determine their linear ordering.

When you create a new UDT, you must ensure that the relational-operator functions can compare two values of the UDT. Otherwise, the comparison cannot occur, and the UDT cannot be used in a B-tree index.

For example, suppose you create the circle opaque type to implement a circle. A circle is a spatial object that might be indexed best with a user-defined secondary-access method such as an R-tree, which handles multidimensional objects. However, you can use the circle data type in a B-tree index if you define the relational operators on the value of its area: one circle is less than a second circle if its area is less than the area of the second.

Changing the Sort Order

A generic B-tree uses the relational operators to determine which value is less than another. These operators use lexicographical sequence (numeric order for numbers, alphabetic order for characters, chronological order for dates and times) for the values that they order.

For some UDTs, the relational operators in the default B-tree operator class might not achieve the order that you want. You can define the relational-operator functions for a particular user-defined type so that the sort order changes from a lexicographical sequence to some other sequence.

For example, suppose you create an opaque data type, ScottishName, that holds Scottish names, and you want to order the data type in a different way than the U.S. English collating sequence. You might want the names McDonald and MacDonald to appear together on a phone list. This data type can use a B-tree index because it defines the relational operators that equate the strings Mc and Mac.

To order the data type in this way, write the relational-operator functions so that they implement this new order. For the strings Mc and Mac to be equal, you must define the relational-operator functions that:

• Accept the opaque data type, ScottishName, in the parameter list
• Contain code that equates Mc and Mac

The following steps use the steps described in Extensions of the btree_ops Operator Class to extend the btree_ops operator class.

You can now create a B-tree index on a ScottishName column:

CREATE TABLE scot_cust
(
   cust_id integer, 
   cust_name ScottishName
   ...
);
CREATE INDEX cname_ix
   ON scot_cust (cust_name);

The optimizer can now choose whether to use the cname_ix index to evaluate the following query:

SELECT * FROM scot_cust 
WHERE cust_name = 'McDonald'::ScottishName