INFORMIX
Extending Informix-Universal Server: Data Types
Chapter 4: Extending an Operator Class
Home Contents Index Master Index New Book

Creating an Operator Class

 For most indexing situations, 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:

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

Universal Server provides the default operator class, btree_ops, for the generic B-tree access method. The following CREATE OPCLASS statement creates the btree_ops operator class:

For more information, see "The Generic B-Tree Operator Class".

You might want to create a new operator class for:

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

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

Defining a New B-Tree 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".) To have a generic B-tree 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

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

    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 C functions for the new B-tree support function that accepts the appropriate data type in its parameter list.

    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:

    You registered these functions in step 2. They must be listed 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.

    6. To create a generic B-tree index that uses the new operator class, specify the name of this operator class in the CREATE INDEX statement.

    By default, the CREATE INDEX statement uses the default operator class for columns and functions in a generic B-tree index. To use a new operator class instead, specify the new operator class name after the column or function name.

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

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:

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 C functions for the new strategy functions: abs_lessthan(), abs_lessthanorequal(), abs_equal(), abs_greater(), and abs_greaterthanorequal().

    Compile these functions and store them in the absbtree.so shared library.

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

    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);

    6. 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 their SQL statements, as in the following example statement:

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:

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:

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.

Defining an Operator Class for Other Secondary Access Methods

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 more information on how to determine the secondary access methods available in your database, see the "INFORMIX-Universal Server Performance Guide."
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 page 4-20). 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.

For example, suppose that your system implements the functions of the rtree secondary access method.

Important: To use an R-tree index, install a spatial DataBlade module such as the Spatial DataBlade module, Geodetic DataBlade, or any other third-party DataBlade module that implements an R-tree index. These spatial DataBlade modules might use the rtree_ops operator class, or they might provide their own operator classes for an R-tree index. For more information on the spatial DataBlade modules, consult the appropriate DataBlade module user guide.
You can create a new operator class, rtree2_ops, for the rtree secondary access method. The new operator class must have the same number of strategy and support functions as the default operator class for rtree, rtree_ops. For more information on the rtree_ops operator class, see page 4-9.

The following steps create the rtree2_ops operator class:

    1. Write C functions for the new strategy functions: rtree2_overlap(), rtree2_equal(), rtree2_contains(), and rtree2_within().

    These functions must have the same return values as their counterparts in the rtree_ops operator class. Compile these functions, store them in the rtree2.so shared library, and put this shared library in the /apps/lib directory.

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

    The following CREATE FUNCTION statements register the four strategy functions that handle the polygon opaque data type:

CREATE FUNCTION Overlap2(polygon, polygon)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/rtree2.so(rtree2_overlap)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION Equal2(polygon, polygon)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/rtree2.so(rtree2_equal)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION Contains2(polygon, polygon)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/rtree2.so(rtree2_contains)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION Within2(polygon, polygon)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/rtree2.so(rtree2_within)'
LANGUAGE C NOT VARIANT;

    3. Write the C functions for the new support functions: rtree2_union(), rtree2_size(), and rtree2_inter().

    These functions must have the same return values as their counterparts in the rtree_ops operator class. Compile these functions and store them in the rtree2.so shared library.

    4. Register the four new support functions with the CREATE FUNCTION statement.

    The following CREATE FUNCTION statements register the support functions that handle the polygon opaque data type:

CREATE FUNCTION Union2(polygon, polygon)
RETURNS polygon
EXTERNAL NAME '/lib/rtree2.so(rtree2_union)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION Size2(polygon, polygon)
RETURNS float
EXTERNAL NAME
'/lib/rtree2.so(rtree2_size)'
LANGUAGE C NOT VARIANT;

CREATE FUNCTION Inter2(polygon, polygon)
RETURNS polygon
EXTERNAL NAME '/lib/rtree2.so(rtree2_inter)'
LANGUAGE C NOT VARIANT;

    5. Create the new rtree2_ops operator class for the rtree secondary access method. CREATE OPCLASS rtree2_ops FOR rtree
    STRATEGIES (Overlap2, Equal2, Contains2, Within2)
    SUPPORT (Union2, Size2, Inter2);

    6. You can now create an R-tree index on a polygon column and associate the new operator class with this column. CREATE TABLE region_tab
    (
    region_id integer,
    region_space polygon
    ...
    );

    CREATE INDEX reg_spc_rix
    ON region_tab (region_space rtree2_ops)
    USING rtree;

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



Extending Informix-Universal Server: Data Types, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.