Extending Informix Dynamic Server 2000 Extending an Operator Class

For most situations, when you build an index, you can use the default operators that are defined for a secondary access method. This section provides a brief introduction to secondary access methods and operator classes.

For a more detailed discussion of secondary access methods and operator classes, see the Performance Guide.

A secondary access method, often called an index, is a set of user-defined functions that build, access, and manipulate an index structure. These functions encapsulate index operations, such as how to scan, insert, delete, or update nodes in an index. A secondary access method describes how to access the data in an index that is built on a column (column index) or on a user-defined function (functional index). Typically, a secondary access method speeds up the retrieval of a type of data.

The database server provides definitions for the following secondary access methods in the system catalog tables of each database:

• A generic B-tree
• An R-tree

DataBlade modules can provide additional secondary access methods for use with user-defined data types. For more information about secondary access methods of DataBlade modules, refer to the user guide for each DataBlade module. For more information about R-trees, refer to the Informix R-Tree Index User's Guide.

In traditional relational database systems, the B-tree access method handles only built-in data types and therefore can compare only two keys of built-in data types. The B-tree index is useful for a query that retrieves a range of data values. To support user-defined data types, the database server provides an extended version of a B-tree, the generic B-tree index.

The database server uses the generic B-tree index as the built-in secondary access method. This secondary access method is registered in the sysams system catalog table with the name btree. When you use the CREATE INDEX statement (without the USING clause) to create an index, the database server creates a generic B-tree index. The following statement creates a B-tree index on the zipcode column of the customer table:

CREATE INDEX zip_ix ON customer (zipcode)

For more information, see the CREATE INDEX statement in the Informix Guide to SQL: Syntax.

The database server can support the R-tree index for columns that contain spatial data such as maps and diagrams. An R-tree index is most beneficial when queries look for objects that are within other objects or for an object that contains one or more objects.

To use an R-tree index, install a spatial DataBlade module such as the Spatial DataBlade module, Geodetic DataBlade module, or any other third-party DataBlade module that implements an R-tree index.

A DataBlade module can provide a user-defined data type to handle a particular type of data. The module might also provide a new secondary access method (index) for the new data type that it defines. For example, the Excalibur Text DataBlade module provides an index to search text data. For more information, refer to the Excalibur Text Search DataBlade Module User's Guide. For more information on the types of data and functions that each DataBlade module provides, refer to the user guide for the DataBlade module. The sysams system catalog table describes the secondary access methods that exist in your database. For information about sysams, see the Informix Guide to SQL: Reference.

An operator class is a group of functions that allow the secondary access method to store and search for values of a particular data type. The query optimizer uses an operator class to determine if an index can process the query with the least cost. For more information on the query optimizer, see the Performance Guide.

The operator-class functions fall into the following categories:

• Strategy functions

The database server uses the strategy functions of a secondary access method to help the query optimizer determine whether a specific index is applicable to a specific operation on a data type. The strategy functions are the operators that can appear in the filter of an SQL statement.

• Support functions

The database server uses the support functions of a secondary access method to build and access the index. End users do not call these functions directly. When an operator in the filter of a query matches one of the strategy functions, the secondary access method uses the support functions to traverse the index and obtain the results.

Each secondary access method has a default operator class associated with it. By default, the CREATE INDEX statement associates the default operator class with an index.

The database server stores information about operator classes in the sysopclasses system catalog table.

The built-in secondary access method, the generic B-tree, has a single operator class defined in the sysopclasses system catalog table. This operator class, called btree_ops, is the default operator class for the btree secondary access method.

The database server uses the btree_ops operator class to specify:

• the strategy functions to tell the optimizer which filters in a query can use a B-tree index.
• the support function to build and search the B-tree index.

The CREATE INDEX statement in Generic B-Tree Index shows how to create a B-tree index whose column uses the btree_ops operator class. This CREATE INDEX statement does not need to specify the btree_ops operator class because btree_ops is the default operator class for the btree access method.

For more information on the btree secondary access method, see
Generic B-Tree Index.

The btree_ops operator class defines the following strategy functions for the btree access method:

• lessthan (<)
• lessthanorequal (<=)
• equal (=)
• greaterthanorequal (>=)
• greaterthan (>)

These strategy functions are all operator functions. That is, each function is associated with an operator symbol; in this case, with a relational-operator symbol. For more information on relational-operator functions, see Relational Operators.

The btree_ops operator class has one support function, a comparison function called compare(). The compare() function is a user-defined function that returns an integer value to indicate whether its first argument is equal to, less than, or greater than its second argument, as follows:

• A value of 0 when the first argument is equal to the second argument
• A value less than 0 when the first argument is less than the second argument
• A value greater than 0 when the first argument is greater than the second argument

The B-tree secondary access method uses the compare() function to traverse the nodes of the generic B-tree index. To search for data values in a generic B-tree index, the secondary access method uses the compare() function to compare the key value in the query to the key value in an index node. The result of the comparison determines if the secondary access method needs to search the next-lower level of the index or if the key resides in the current node.

The generic B-tree access method also uses the compare() function to perform the following tasks for generic B-tree indexes:

• Sort the keys before building the index
• Determine the linear ordering of keys in a generic B-tree index
• Evaluate the relational operators

The database server uses the compare() function to evaluate comparisons in the SELECT statement. To provide support for these comparisons for opaque data types, you must write the compare() function. For more information, see Comparing Data.

The R-tree secondary access method has an operator class defined in the sysopclasses system catalog table. This operator class, called rtree_ops, is the default operator class for the rtree secondary access method. The database server defines the default R-tree operator class in the system catalog tables but does not provide the operator-class functions to implement this operator class.

To use an R-tree index, install a spatial DataBlade module such as the Spatial DataBlade module, Geodetic DataBlade module, or any other third-party DataBlade module that implements an R-tree index. For more information on R-tree indexes, refer to Informix R-Tree Index User's Guide. For more information on the spatial DataBlade modules, consult the appropriate DataBlade module user guide.