Optimize queries

The WHERE clause of the SELECT statement controls the amount of information that the query evaluates. This clause can consist of a comparison condition, which evaluates to a BOOLEAN value. Therefore, a comparison condition can contain a Boolean function; that is, it can contain a user-defined function that returns a BOOLEAN value. Boolean functions can act as filters in queries, as the following table shows.

Table 1. Boolean functions valid in a comparison condition
Comparison condition	Operator symbol	Associated user-defined function
Relational operator	=, !=, <> <, <= >, >=	equal(), notequal(), notequal() lessthan(), lessthanorequal() greaterthan(), greaterthanorequal()
LIKE, MATCHES	None	like(), matches()
Boolean function	None	Name of a user-defined function that returns a BOOLEAN value

The Boolean functions in Table 1 can act as filters in queries. To optimize queries that use these functions as filters, you can define the following UDR-optimization functions.

Type of optimization	Description
Negator function	Calculate the NOT condition of the Boolean expression
Selectivity and cost functions	Provide an estimate of the number of rows that the filter will return

Tip: A WHERE clause can also consist of a condition with a subquery. However, conditions with subqueries do not evaluate to a Boolean function. Therefore, they do not require UDR-optimization functions. For more information about conditions with subqueries, see your and the Condition segment of the HCL Informix® Guide to SQL: Syntax.