search-condition |--+-----+--+-predicate--+-------------------------------+-+----> '-NOT-' | '-SELECTIVITY--numeric-constant-' | '-(search-condition)---------------------------' .------------------------------------------------------------------------. V | >----+--------------------------------------------------------------------+-+--| '-+-AND-+--+-----+--+-predicate--+-------------------------------+-+-' '-OR--' '-NOT-' | '-SELECTIVITY--numeric-constant-' | '-(search-condition)---------------------------'
A search condition specifies a condition that is "true," "false," or "unknown" about a given value, row, or group.
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.
SELECT *
FROM STORES
WHERE foo(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).
SELECT *
FROM customers
WHERE within(location, :sanJose) = 1 SELECTIVITY 0.2
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 above 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 above user-defined predicate 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.