An index scan may be used for a query that has an OR predicate.
How OR Predicates are Optimized |
 |
Most predicates involving OR
factors are transformed to conjunctive normal form to make
the choice of an index scan during optimization more likely.
In addition, the optimization of OR predicates
involves internally ANDing additional
factors to the predicate you supply in order to eliminate duplicates.
Conjunctive normal form expresses a predicate as the conjunction
of factors rather than the disjunction of factors. For
example, if a predicate has the following elements:
(c1=10 AND c2=20) OR (c1=10 AND c3=30)
|
there are two factors in disjunctive normal form. ALLBASE/SQL transforms the predicate as follows:
(c1=10 OR c1=10) AND (c2=20 OR c1=10) AND
(c1=10 OR c3=30) AND (c2=20 OR c3=30)
|
Now there are 4 factors in conjunctive normal form.
The transformation of a predicate into conjunctive normal form
increases
the number of factors in the predicate. This can result in exceeding
the maximum number of factors in a predicate (currently 256).
You can avoid this problem by writing your predicates in conjunctive
normal form yourself, as in the following:
(c1=10) AND (c2=20 OR c3=30)
|
The only exception to this rule is a predicate containing OR and
BETWEEN, as in the following:
(c1 BETWEEN 10 AND 20) OR (c2 BETWEEN 30 AND 40)
|
Each BETWEEN predicate is actually a conjunction of two range
predicates:
(c1 >= 10 AND c1 <= 20) OR (c2 >= 30 AND c2 <= 40)
|
Such a predicate is not in conjunctive normal form. However, in
this case, you should not rewrite the predicate in
conjunctive normal form, nor does ALLBASE/SQL transform the
predicate. Both range predicates from a BETWEEN predicate can be
used in a single index scan. Therefore, ALLBASE/SQL can make
best use of this predicate when it is in the original form.
Choosing an Index for OR Factors |
|
For index scan plans to be chosen for an OR factor (for example, c1=10 OR c2=20), the following conditions must be met:
All columns involved in the OR factor must come from a single table.
Each column involved in the OR factor must have an index defined on it.
The OR factor must require no data type conversions that result in
the loss of significant information.
Based on these conditions, here are some suggestions for query and
index design:
If a query predicate includes only OR factors (all columns in
one OR factor from a single table), it is a good idea to define a
multicolumn index on all columns involved.
If a query predicate includes both OR factors and simple
EQUAL factors (for example, (c1=10 OR c2=20) AND c3=30), the
EQUAL factor (c3=30) may yield a cheaper plan
than the OR factor. In such cases, it is a
good idea to ensure that there is an
index on the columns involved in the EQUAL factor.
To ensure that no unacceptable data type conversions are required, columns and
expressions being compared in the factors should be of compatible data
types. For example, if c1 is a column of type integer and c2 is
a column of type decimal (2,1), the following predicate would require
unacceptable data type conversions, and therefore index scan plans
may not be chosen:
The following predicate would not require any unacceptable
data type conversions, and therefore index scan plans might be chosen:
