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.
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:
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.
- 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 functionmyfunction
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
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
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
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.