Extending Informix Dynamic Server 2000 Improving UDR Performance

A negator function takes the same arguments as its companion function, in the same order, but returns the Boolean complement. That is, if a function returns TRUE for a given set of arguments, its negator function returns FALSE when passed the same arguments, in the same order. In certain cases, the database server can process a query more efficiently if the sense of the query is reversed; that is, if the query is Is x greater than y? instead of Is y less than or equal to x?

The NEGATOR modifier of the CREATE FUNCTION statement names a companion function, a negator function, to the current function. When you provide a negator function, the optimizer can use a negator function instead of the function you specify when it is more efficient to do so. If a function has a negator function, any user who executes the function must have Execute privilege on both the function and its negator. In addition, a function must have the same owner as its negator function.

You can write negator functions in SPL or as external functions. The following example shows CREATE FUNCTION statements that specify negator functions:

CREATE ROW TYPE complex(real FLOAT, imag FLOAT);

CREATE FUNCTION equal (c1 complex, c2 complex)
	RETURNING BOOLEAN WITH (NEGATOR = notequal)
	DEFINE a BOOLEAN;
	IF (c1.real = c2.real) AND (c1.imag = c2.imag) THEN
		LET a = 't';
	ELSE
		LET a = 'f';
	END IF;
	RETURN a;
END FUNCTION;


CREATE FUNCTION notequal (c1 complex, c2 complex)
	RETURNING BOOLEAN WITH (NEGATOR = equal)
	DEFINE a BOOLEAN;
	IF (c1.real != c2.real) OR (c1.imag != c2.imag) THEN
		LET a = 't';
	ELSE
		LET a = 'f';
	END IF;
	RETURN a;
END FUNCTION;