User-defined operator classes

The CREATE INDEX statement specifies the operator class to use for each component of an index. If you do not specify an operator class, the CREATE INDEX statement 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 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 Identifying 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 about how to create an operator class, see HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.