Converting clauses to search argument equivalents

Preprocessing looks for some query clauses that it can convert to the form used for search arguments (SARGs). These are listed in Table 2-1.

Table 2-1: Search argument equivalents

Clause

Conversion

between

Converted to >= and <= clauses. For example, between 10 and 20 is converted to >= 10 and <= 20.

like

If the first character in the pattern is a constant, like clauses can be converted to greater than or less than queries. For example, like "sm%" becomes >= "sm" and < "sn".

If the first character is a wildcard, a clause such as like "%x" cannot use an index for access, but histogram values can be used to estimate the number of matching rows.

in (values_list)

Converted to a list of or queries, that is, int_col in (1, 2, 3) becomes int_col = 1 or int_col = 2 or int_col = 3. The maximum number of elements in an IN-list is 1025.