Implementing a commutator function with a C user-defined function

A commutator function is a special UDR that is associated with a user-defined function.

A UDR is commutator of another user-defined function if either of the following statements is true:

The UDR takes the same arguments as its associated user-defined function but in opposite order.
The UDR returns the same result as the associated user-defined function.

For example, the lessthan() and greaterthanorequal() functions are commutators of one another because the following two expressions yield the same result:

a < b
b >= a

In the following SELECT statement, the optimizer can choose whether it is more cost effective to execute lessthan(a, b) or greaterthanorequal(b, a) in the WHERE clause:

SELECT * FROM tab1 WHERE lessthan(a, b);

The optimizer can choose to invoke the function greaterthanorequal(b, a) if there is no index on lessthan() and there exists an index on greaterthanorequal().

To implement a commutator function with a C user-defined function:

Declare the commutator function so that its parameters are in the reverse order as its associated user-defined function and its return value is the same as its user-defined function.
Within the commutator function, perform the tasks to evaluate the commutable operation of the associated Boolean user-defined function.
Register the commutator function as a user-defined function with the CREATE FUNCTION statement.
Associate the user-defined function and its commutator function when you register the user-defined function.
Specify the name of the commutator function with the COMMUTATOR routine modifier in the CREATE FUNCTION statement that registers the user-defined function.

The following CREATE FUNCTION statements register the commute_func1() and func1() user-defined functions:

CREATE FUNCTION commute_func1(b CHAR(20), a INTEGER)
RETURNS INTEGER
EXTERNAL NAME '/usr/local/lib/udrs/udrs.so'
LANGUAGE C;

CREATE FUNCTION func1(a INTEGER, b CHAR(20))
RETURNS INTEGER
WITH (COMMUTATOR = commute_func1)
EXTERNAL NAME '/usr/local/lib/udrs/udrs.so'
LANGUAGE C;

Important: The generic B-tree secondary-access method does not check for commutator functions registered with the COMMUTATOR routine modifier. Instead, it performs its own internal optimization for commutable operations. However, commutator functions registered with COMMUTATOR are used by the R-tree secondary-access method when UDRs occur in fragmentation expressions.

For more information about commutator functions, see the HCL Informix® User-Defined Routines and Data Types Developer's Guide. For information about how to determine if a user-defined function has a commutator function, see Determine if a function is a commutator function.