Extending an Existing Operator Class
The operator-class functions of an operator class can be defined only for the data types that are currently defined in a database. When you create a new user-defined data type, you must determine whether you need to create operator-class functions that are defined for this new 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 having the same name but each having a different parameter list. Universal 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 Extending INFORMIX-Universal Server: User-Defined Routines.
When you create a user-defined data type, you must perform the following steps:
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(s).
To allow end users to use the user-defined type with the operators that are associated with the secondary access method, write new strategy functions that handle the new data type. You also need to write new support functions to handle this new data type.
Universal Server provides the B-tree and R-tree secondary access methods for indexes. The following sections describe how to extend the default operator classes of each of these secondary access methods.
Extending the btree_ops Operator Class
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 also extend these operator-class functions to handle the data type.
How to Extend btree_ops
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.
To extend the default operator class for a generic B-tree index
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 C functions (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 operator functions for the user-defined data type, the generic B-tree index uses them as its strategy functions.
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 a C function for the B-tree support function, compare(), that accepts the user-defined data type in its parameter list.
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.
4. Register the support function 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 type. For more information, see "Comparing Data".
5. 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 INFORMIX-Universal Server Performance Guide. For an example of how to extend the generic B-tree index for an opaque-type column, see "A Fixed-Length Opaque Type: circle".
These 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 "Defining a New B-Tree Operator Class".
Reasons to Extend 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 "The B-Tree Strategy Functions".) By default, a generic B-tree index can handle 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 be able 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:
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.
For a particular user-defined data type, 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 is intended to index 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 tree and in which node to store a value.
By default, 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 that handles two-dimensional objects. However, this data type can be used in a B-tree index if it defines 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 Type: circle".
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 or numbers, alphabetic order for characters, chronological order for dates and times) for the values 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 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 define the relational-operator functions for a particular user-defined type so that they change from 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 be equal, you must define the relational-operator functions that:
The following steps use the steps in "How to Extend btree_ops" to extend the btree_ops operator class to support the ScottishName data type:
1. Write C functions for the strategy functions that handle the ScottishName data type: lessthan(), lessthanorequal(), equal(), greaterthan(), greaterthanorequal().
Compile these functions and store them in the btree.so shared library and put this shared library in the /apps/lib directory.
2. Register the five new strategy functions with the CREATE FUNCTION statement.
The following CREATE FUNCTION statements register the strategy functions that handle the ScottishName opaque data type:
CREATE FUNCTION lessthan (ScottishName, ScottishName)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/btree.so(scottish_lessthan)"
LANGUAGE C NOT VARIANT;
CREATE FUNCTION lessthanorequal(ScottishName,
ScottishName)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/btree.so(scottish_lessthanorequal)'
LANGUAGE C NOT VARIANT;
CREATE FUNCTION equal(ScottishName, ScottishName)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/btree.so(scottish_equal)'
LANGUAGE C NOT VARIANT;
CREATE FUNCTION
greaterthanorequal(ScottishName, ScottishName)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/btree.so(scottish_greaterthanorequal)'
LANGUAGE C NOT VARIANT;
CREATE FUNCTION greaterthan(ScottishName,
ScottishName)
RETURNS boolean
EXTERNAL NAME
'/apps/lib/btree.so(scottish_greaterthan)'
LANGUAGE C NOT VARIANT;
3. Write the C function for the compare() support function that handles the ScottishName data type.
Compile this function and store it in the btree.so shared library.
4. Register the new compare() support function with the CREATE FUNCTION statement.
CREATE FUNCTION compare(ScottishName, ScottishName)
RETURNS integer
EXTERNAL NAME
'/apps/lib/btree.so(scottish_compare)'
LANGUAGE C NOT VARIANT
5. 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:
Extending the rtree_ops Operator Class
To be able to use an R-tree index, you must first 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 either:
If an existing R-tree operator class does not support your user-defined data type, you can try to extend the operator-class functions to handle this data type. The ability to extend this operator class depends on whether your user-defined data type can logically be indexed by an R-tree index.
For more information on the R-tree secondary access method, see page 4-9.
|
