SQL Reference

Search Conditions

 
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 row.

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 14, in which P and Q are any predicates:

Table 14. 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 11. Search Conditions Evaluation Order

SELECTIVITY value

The SELECTIVITY clause is used to indicate to DB2 what the expected selectivity percentage is for the predicate. SELECTIVITY can be specified only when the predicate is a user-defined predicate.

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 foo is defined with PREDICATES WHEN=1..., then the following use of SELECTIVITY is valid:

	
     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.FUNCTIONS 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. (For more information about the within and distance UDFs, see the Examples section of CREATE FUNCTION (External Scalar).)

     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 of one store to another is computed by the radius of the city that the customers live in.

     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.