Extending Informix Dynamic Server 2000 Extending an Operator Class

For most indexing, the operators in the default operator class of a secondary access method provide adequate support. However, when you want to order the data in a different sequence than the default operator class provides, you can define a new operator class for the secondary access method.

The CREATE OPCLASS statement creates an operator class. It provides the following information about the operator class to the database server:

• The name of the operator class
• The name of the secondary access method with which to associate the functions of the operator class
• The names and, optionally, the parameters of the strategy functions
• The names of the support functions

The database server stores this information in the sysopclasses system catalog table. You must have the Resource privilege for the database or be the DBA to create an operator class.

The database server provides the default operator class, btree_ops, for the generic B-tree access method. The following CREATE OPCLASS statement creates a new operator class for the generic B-tree access method:

CREATE OPCLASS new_btree_ops FOR btree

	STRATEGIES (lessthan, lessthanorequal, equal,

		greaterthanorequal, greaterthan)

	SUPPORT(compare);

For more information, see Generic B-Tree Index.

You might want to create a new operator class for:

• the generic B-tree secondary access method.

A new operator class can provide an additional sort order for all data types that the B-tree index can handle.

• any user-defined secondary access methods.

A new operator class can provide additional functionality to the strategy functions of the operator class.

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 on this sequence, see Changing 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.

1. Write external (C or Java) functions for five new 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 on 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 external functions 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 user-defined data types. However, the return type must be integer. For more information on 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 on how to use the CREATE OPCLASS statement, refer to the Informix Guide to SQL: Syntax.

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.

As an example, suppose you want to define a new ordering for integers. The lexicographical sequence of the default B-tree operator class orders integers numerically: -4 < -3 < -2 < -1 < 0 < 1 < 2 < 3. Instead, you might want the numbers -4, 2, -1, -3 to appear in order of absolute value.

-1, 2, -3, -4

To obtain the absolute-value order, you must define external functions that treat negative integers as positive integers. The following steps create a new operator class called abs_btree_ops with strategy and support functions that provide the absolute-value order:

1. Write and register external functions for the new strategy functions: abs_lessthan(), abs_lessthanorequal(), abs_equal(), abs_greaterthan(), and abs_greaterthanorequal().

For more information, refer to Chapter 4, Developing a User-Defined Routine.

2. Register the five new strategy functions with the CREATE FUNCTION statement.

The following CREATE FUNCTION statements register the five strategy functions that handle the INTEGER data type:

CREATE FUNCTION abs_lt(integer, integer)
RETURNS boolean
EXTERNAL NAME '/lib/absbtree.so(abs_lessthan)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION abs_lte(integer, integer)
RETURNS boolean
EXTERNAL NAME
'/lib/absbtree.so(abs_lessthanorequal)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION abs_eq(integer, integer)
RETURNS boolean
EXTERNAL NAME '/lib/absbtree.so(abs_equal)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION abs_gte(integer, integer)
RETURNS boolean
EXTERNAL NAME
'/lib/btree1.so(abs_greaterthanorequal)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION abs_gt(integer, integer)
RETURNS boolean
EXTERNAL NAME '/lib/absbtree.so(abs_greaterthan)'
LANGUAGE C NOT VARIANT;


3. Write the C function for the new support function: abs_compare().

Compile this function and store it in the absbtree.so shared-object file.

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

The following CREATE FUNCTION statement registers the support function that handles the INTEGER data type:

CREATE FUNCTION abs_cmp(integer, integer)
RETURNS integer
EXTERNAL NAME '/lib/absbtree.so(abs_compare)'
LANGUAGE C NOT VARIANT;

5. Create the new abs_btree_ops operator class for the B-tree secondary access method.
CREATE OPCLASS abs_btree_ops FOR btree
STRATEGIES (abs_lt, abs_lte, abs_eq, abs_gte,
abs_gt)
SUPPORT (abs_cmp);

You can now create a B-tree index on an INTEGER column and associate the new operator class with this column.

CREATE TABLE cust_tab
(
cust_name varchar(20),
cust_num integer
...
);

CREATE INDEX c_num1_ix
ON cust_tab (cust_num abs_btree_ops);

The c_num1_ix index uses the new operator class, abs_btree_ops, for the cust_num column. An end user can now use the absolute value functions in SQL statements, as in the following example:

SELECT * FROM cust_tab WHERE abs_lt(cust_num, 7)

In addition, because the abs_lt() function is part of an operator class, the query optimizer can use the c_num1_ix index when it looks for all cust_tab rows with cust_num values between -7 and 7. A cust_num value of -8 does not satisfy this query.

The default operator class is still available for indexes. The following CREATE INDEX statement defines a second index on the cust_num column:

CREATE INDEX c_num2_ix ON cust_tab (cust_num);

The c_num2_ix index uses the default operator class, btree_ops, for the cust_num column. The following query uses the operator function for the default less than (<) operator:

SELECT * FROM cust_tab WHERE lessthan(cust_num, 7)

The query optimizer can use the c_num2_ix index when it looks for all cust_tab rows with cust_num values less than 7. A cust_num value of -8 does satisfy this query.

You can also define operator classes for user-defined secondary access methods. A user-defined secondary access method is one that a database developer has defined to implement a particular type of index. These access methods might have been defined in the database by a DataBlade module.

Tip: You can examine the sysams system catalog table to determine which secondary access methods your database defines. For information on the columns of the sysams system catalog table, see the "Informix Guide to SQL: Reference."

You perform the same steps to define an operator class on a user-defined secondary access method as you use to define an operator class on the generic B-tree index. (See Creating a New B-Tree Operator Class.) The only difference is that to create the index, you must specify the name of the user-defined secondary access method in the USING clause of the CREATE INDEX statement.