The optimizer looks for query clauses to convert to the form used for search arguments. These are listed in Table 1-2.
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. If the number of IN list elements is less than 40 then the optimizer uses OR optimization. If the number of elements is greater than 40, then the optimizer models this as a work table of values which is joined to the column associated with the IN list. There is no limit on the number of members in the IN list. |