B-tree support function

The btree_ops operator class has one support function, a comparison function called compare(). 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 the HCL OneDB™ 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.