Change 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.

The relational-operator functions use the code-set order for character data types (CHAR, VARCHAR, LVARCHAR, and IDSSECURITYLABEL) and a localized order for the NCHAR and NVARCHAR data types. When you use the default locale, U.S. English, code-set order and localized order are those of the ISO 8895-1 code set. When you use a nondefault locale, these two orders might be different. For more information about locales, see the HCL OneDB™ GLS User's Guide.

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.
Tip: When you extend an operator class, you can change the sort order for a UDT. To provide an alternative sort order for all data types that the B-tree handles, you must define a new operator class. For more information, see Creating a new B-tree operator class.

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

To support the ScottishName data type

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

  1. Prepare and register the strategy functions that handle the ScottishName data type: lessthan(), lessthanorequal(), equal(), greaterthan(), and greaterthanorequal().

    For more information, refer to Develop a user-defined routine.

  2. Prepare and register the external function for the compare() support function that handles the ScottishName data type.
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