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)

Using OR Predicates

Technical documentation

» Table of Contents

» Index

How OR Predicates are Optimized

Choosing an Index for OR Factors