Extending Informix Dynamic Server 2000 Extending an Operator Class

You can define operator-class functions of an operator class only for existing data types. When you create a user-defined data type, you must determine whether you need to create operator-class functions for this data type. The creation of new operator-class functions that have the same names as the existing operator class functions is the most common way to extend an existing operator class.

To extend the functionality of an operator-class function, write a function that has the same name and return value. You provide parameters for the new data type and write the function to handle the new parameters. Routine overloading allows you to create many functions, all with the same name but each with a different parameter list. The database server then uses routine resolution to determine which of the overloaded functions to use based on the data type of the value. For more information on routine overloading and routine resolution, see Chapter 3, Running a User-Defined Routine.

1. Decide which of the secondary access methods can support the user-defined data type.
2. Extend the operator classes of the chosen secondary access method or methods.

To allow end users to use the user-defined type with the operators that are associated with the secondary access method, write new strategy and support functions to handle this new data type.

Before the database server can support generic B-tree indexes on a user-defined data type, 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.

Important: You cannot extend the btree_ops operator class for the built-in data types.

Once you determine how you want to implement the relational operators for a user-defined data type, 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.

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

You can write strategy functions as external functions or as SPL functions. 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 user-defined data type, 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 have already registered the relational-operator functions, you do not need to reregister them as strategy functions.

3. Write an external function for the B-tree support function, compare(), that accepts the user-defined data type in its parameter list. (The compare() function cannot be in SPL.)

Compile this function and store it in a shared-object file. For more information, refer to Chapter 4, Developing a User-Defined Routine.

The compare() function also provides support for a user-defined data type 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 user-defined data type, the generic B-tree index uses it as its support function.

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

For more information on strategy functions, see B-Tree Strategy Functions. For information on relational operators for an opaque data type, see Comparing Data.

After you have registered the support function, you can use the CREATE INDEX statement to create a B-tree index on the column of the table that contains the user-defined data type. 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 user-defined data type.

The query optimizer can now consider use of this generic B-tree index to execute queries efficiently. For more information on the performance aspects of column indexes, see the Performance Guide. For an example of how to extend the generic B-tree index for an opaque-type column, see A Fixed-Length Opaque Data Type: circle.

Important: When the database server uses a generic B-tree index to process an ORDER BY clause in a SELECT statement, the database server uses the btree_ops support function called compare(). However, the optimizer does not use the B-tree index to perform ORDER BY if the index does not use the btree_ops operator class.

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.

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 user-defined data type, you extend the generic B-tree so that it can handle the user-defined data type 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 user-defined data type, 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 user-defined data type, the relational-operator functions must determine what constitutes an ordered sequence of the values.

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 the built-in data types. (For more information on the built-in data types, see the chapter on data types in the 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 user-defined data type, you must ensure that the relational-operator functions can compare two values of the user-defined data type. Otherwise, the comparison cannot occur, and the user-defined data type 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. For more information on the circle opaque type, see A Fixed-Length Opaque Data Type: circle.

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, and LVARCHAR) 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 on locales, see the Informix Guide to GLS Functionality.

For some user-defined data types, 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 lexicographical sequence to some other sequence.

Tip: When you extend an operator class, you can change the sort order for a user-defined data type. 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 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.

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

For more information, refer to Chapter 4, Developing 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