HP 3000 Manuals HP 3000 Manuals
Using OR Predicates
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:
(c1=1.0 OR c2=2.00)
The following predicate would not require any unacceptable data
type conversions, and therefore index scan plans might be chosen:
(c1=1 OR c2=2.0)