Search arguments converted to equivalent arguments

The optimizer looks for query clauses to convert to the form used for search arguments. These are listed in Table 1-2.

Table 1-2: 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 may 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