Choosing Operator Classes for Indexes

For most situations, use the default operators that are defined for a secondary-access method. However, when you want to order the data in a different sequence or provide index support for a user-defined data type, you must extend an operator class. For more information on how to extend an operator class, see IBM Informix: User-Defined Routines and Data Types Developer's Guide.

Operator Classes

An operator class is a set of function names that is associated with a secondary-access method. These functions allow the secondary-access method to store and search for values of a particular data type. The query optimizer for the database server uses an operator class to determine if an index can process the query with the least cost. An operator class indicates two things to the query optimizer:

• Which functions that appear in an SQL statement can be evaluated with a given index

  These functions are called the strategy functions for the operator class.

• Which functions the index uses to evaluate the strategy functions

  These functions are called the support functions for the operator class.

With the information that the operator class provides, the query optimizer can determine whether a given index is applicable to the query. The query optimizer can consider whether to use the index for the given query when the following conditions are true:

• An index exists on the particular column or columns in the query.
• For the index that exists, the operation on the column or columns in the query matches one of the strategy functions in the operator class associated with the index.

The query optimizer reviews the available indexes for the table or tables and matches the index keys with the column specified in the query filter. If the column in the filter matches an index key, and the function in the filter is one of the strategy functions of the operator class, the optimizer includes the index when it determines which query plan has the lowest execution cost. In this manner, the optimizer can determine which index can process the query with the least cost.

Dynamic Server stores information about operator classes in the sysopclasses system catalog table.

Strategy and Support Functions

Dynamic 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. If an index exists and the operator in the filter matches one of the strategy functions in the operator class, the optimizer considers whether to use the index for the query.

Dynamic Server uses the support functions of a secondary-access method to build and access the index. These functions are not called directly by end users. When an operator in the query filter matches one of the strategy functions, the secondary-access method uses the support functions to traverse the index and obtain the results. Identification of the actual support functions is left to the secondary-access method.

Default Operator Classes

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. For example, the following CREATE INDEX statement creates a B-tree index on the postalcode column and automatically associates the default B-tree operator class with this column:

CREATE INDEX postal_ix ON customer(postalcode)

For more information on how to specify a new default operator class for an index, see Using an Operator Class.

Built-In B-Tree Operator Class

The built-in secondary-access method, the generic B-tree, has a default operator class called btree_ops defined in the sysopclasses system catalog table. By default, the CREATE INDEX statement associates the btree_ops operator class with it when you create a B-tree index. For example, the following CREATE INDEX statement creates a generic B-tree index on the order_date column of the orders table and associates with this index the default operator class for the B-tree secondary-access method:

CREATE INDEX orddate_ix ON orders (order_date)

Dynamic 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

B-Tree Strategy Functions

The btree_ops operator class defines the following names of 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 IBM Informix: User-Defined Routines and Data Types Developer's Guide.

When the query optimizer examines a query that contains a column, it checks to see if this column has a B-tree index defined on it. If such an index exists and if the query contains one of the relational operators that the btree_ops operator class supports, the optimizer can choose a B-tree index to execute the query.

B-Tree Support Function

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 the index is built
• Determine the linear order of keys in a generic B-tree index
• Evaluate the relational operators
• Search for data values in an index

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 IBM Informix: User-Defined Routines and Data Types Developer's Guide.

The database server also uses the compare() function when it uses a B-tree index to process an ORDER BY clause in a SELECT statement. However, the optimizer does not use the index to perform an ORDER BY operation if the index does not use the btree-ops operator class.

Determining the Available Operator Classes

The database server provides the default operator class for the built-in secondary-access method, the generic B-tree index. In addition, your environment might have installed DataBlade modules that implement other operator classes. All operator classes are defined in the sysopclasses system catalog table.

To determine the operator classes that are available for your database, query the sysopclasses system catalog table with the following SELECT statement:

SELECT opclassid, opclassname, amid, am_name
FROM sysopclasses, sysams
WHERE sysopclasses.amid = sysams.am_id

This query returns the following information:

• The opclassid and opclassname columns identify the operator class.
• The am_id and am_name columns identify the associated secondary-access methods.

By default, the database server provides the following definitions in the sysopclasses system catalog table for two operator classes, btree_ops and rtree_ops.

If you find additional rows in the sysopclasses system catalog table (rows with opclassid values greater than 2), your database supports user-defined operator classes. Check the value in the amid column to determine the secondary-access methods to which the operator class belongs.

The am_defopclass column in the sysams system catalog table stores the operator-class identifier for the default operator class of a secondary-access method. To determine the default operator class for a given secondary-access method, you can run the following query:

SELECT am_id, am_name, am_defopclass, opclass_name
FROM sysams, sysopclasses
WHERE sysams.am_defopclass = sysopclasses.opclassid

By default, the database server provides the following default operator classes.

For more information on the columns of the sysopclasses and sysams system catalog tables, see the IBM Informix: Guide to SQL Reference. For information on how to determine the access methods that are available in your database, see Determining the Available Access Methods.

Using an Operator Class

The CREATE INDEX statement specifies the operator class to use for each component of an index. If you do not specify an operator class, CREATE INDEX uses the default operator class for the secondary-access method that you create. You can use a user-defined operator class for components of an index. To specify a user-defined operator class for a particular component of an index, you can:

• Use a user-defined operator class that your database already defines.
• Use an R-tree operator class, if your database defined the R-tree secondary-access method. For more information about R-trees, see the IBM Informix: R-Tree Index User's Guide.

If your database supports multiple-operator classes for the secondary-access method that you want to use, you can specify which operator classes the database server is to use for a particular index. For information on how to determine the operator classes that your database defines, see Determining the Available Operator Classes.

Each part of a composite index can specify a different operator class. You choose the operator classes when you create the index. In the CREATE INDEX statement, you specify the name of the operator class to use after each column or function name in the index-key specification. Each name must be listed in the opclassname column of the sysopclasses system catalog table and must be associated with the secondary-access method that the index uses.

For example, if your database defines the abs_btree_ops secondary-access method to define a new sort order, the following CREATE INDEX statement specifies that the table1 table associates the abs_btree_ops operator class with the col1_ix B-tree index:

CREATE INDEX col1_ix ON table1(col1 abs_btree_ops)

The operator class that you specify in the CREATE INDEX statement must already be defined in the sysopclasses system catalog with the CREATE OPCLASS statement. If the operator class has not yet been defined, the CREATE INDEX statement fails. For information on how to create an operator class, see IBM Informix: User-Defined Routines and Data Types Developer's Guide.