Search conditions

A search condition specifies a condition that is "true," "false," or "unknown" about a given value, row, or group. A search condition can also be a Boolean column, value, or literal.

search-condition

The result of a search condition is derived by application of the specified logical operators (AND, OR, NOT) to the result of each specified predicate. If logical operators are not specified, the result of the search condition is the result of the specified predicate.

AND and OR are defined in Table 1, in which P and Q are any predicates:

Table 1. Truth Tables for AND and OR
P	Q	P AND Q	P OR Q
True	True	True	True
True	False	False	True
True	Unknown	Unknown	True
False	True	False	True
False	False	False	False
False	Unknown	False	Unknown
Unknown	True	Unknown	True
Unknown	False	False	Unknown
Unknown	Unknown	Unknown	Unknown

NOT(true) is false, NOT(false) is true, and NOT(unknown) is unknown.

Search conditions within parentheses are evaluated first. If the order of evaluation is not specified by parentheses, NOT is applied before AND, and AND is applied before OR. The order in which operators at the same precedence level are evaluated is undefined to allow for optimization of search conditions.

Figure 1. Search Conditions Evaluation Order

SELECTIVITY numeric-constant

The SELECTIVITY clause is used to indicate what the expected selectivity percentage is for the predicate. SELECTIVITY can be specified for the following predicates:

A user-defined predicate, regardless of the DB2_SELECTIVITY query compiler registry variable setting
A basic predicate in which at least one expression contains host variables or parameter markers. Specifying SELECTIVITY for this type of predicate applies only when the DB2_SELECTIVITY query compiler registry variable is set to YES.
Any predicate (except REGEXP_LIKE) when the DB2_SELECTIVITY query compiler registry variable is set to ALL. The following predicates allow the SELECTIVITY clause but the value is ignored:
- Basic predicate (with row-value-expression)
- BETWEEN predicate
In these cases, you must rewrite the predicate into multiple basic predicates, each with a separate SELECTIVITY clause. A SELECTIVITY value is not applied if the predicate is modified by a query rewrite rule.

A user-defined predicate is a predicate that consists of a user-defined function invocation, in the context of a predicate specification that matches the predicate specification on the PREDICATES clause of CREATE FUNCTION. For example, if the function myfunction is defined with PREDICATES WHEN=1..., then the following use of SELECTIVITY is valid:

   SELECT *
     FROM STORES
     WHERE myfunction(parm,parm) = 1 SELECTIVITY 0.004

The selectivity value must be a numeric literal value in the inclusive range from 0 to 1 (SQLSTATE 42615). If SELECTIVITY is not specified, the default value is 0.01 (that is, the user-defined predicate is expected to filter out all but one percent of all the rows in the table). The SELECTIVITY default can be changed for any given function by updating its SELECTIVITY column in the SYSSTAT.ROUTINES view. An error will be returned if the SELECTIVITY clause is specified for a non user-defined predicate (SQLSTATE 428E5).

A user-defined function (UDF) can be applied as a user-defined predicate and, hence, is potentially applicable for index exploitation if:

The predicate specification is present in the CREATE FUNCTION statement
The UDF is invoked in a WHERE clause being compared (syntactically) in the same way as specified in the predicate specification
There is no negation (NOT operator)

Examples

In the following query, the within UDF specification in the WHERE clause satisfies all three conditions and is considered a user-defined predicate.

   SELECT *  
     FROM customers 
     WHERE within(location, :sanJose) = 1 SELECTIVITY  0.2

However, the presence of within in the following query is not index-exploitable due to negation and is not considered a user-defined predicate.

   SELECT *  
     FROM customers 
     WHERE NOT(within(location, :sanJose) = 1) SELECTIVITY  0.3

In the next example, consider identifying customers and stores that are within a certain distance of each other. The distance from one store to another is computed by the radius of the city in which the customers live.

   SELECT *  
     FROM customers, stores 
     WHERE distance(customers.loc, stores.loc) < 
       CityRadius(stores.loc) SELECTIVITY 0.02

In the preceding query, the predicate in the WHERE clause is considered a user-defined predicate. The result produced by CityRadius is used as a search argument to the range producer function.

However, since the result produced by CityRadius is used as a range producer function, the user-defined predicate shown previously will not be able to make use of the index extension defined on the stores.loc column. Therefore, the UDF will make use of only the index defined on the customers.loc column.