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 Boolean functions valid in a comparison condition 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 Informix® Performance Guide and the Condition segment of the Informix® Guide to SQL: Syntax.