Identifying the available operator classes

You can identify the operator classes that are available for your database by querying the sysopclasses system catalog table.

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


Access Method	opclassid Column	opclassname Column	amid Column	am_name Column
Generic B-tree	1	btree_ops	1	btree
R-tree	2	rtree_ops	2	rtree

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.


Access Method	am_id Column	am_name Column	am_defopclass Column	opclass_name Column
Generic B-tree	1	btree	1	btree_ops
R-tree	2	rtree	2	rtree_ops

For more information about the columns of the sysopclasses and sysams system catalog tables, see the HCL OneDB™ Guide to SQL: Reference. For information about how to determine the access methods that are available in your database, see Identifying the available access methods.