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