Database Reference
In-Depth Information
OR Operator and Boolean Predicates
Even simple predicates may be difficult for an optimizer if they are ORed with
other predicates; the predicate may be able to participate in defining an index
slice only with
multiple index access
, which will be discussed in Chapter 10.
The following examples (SQL 6.4A and B) should make this clear. Let us
assume there is an index (A, B) on TABLE.
SQL 6.4A
SELECT
A, B, C
FROM
TABLE
WHERE
A > :A
AND
B>:B
SQL 6.4B
SELECT
A, B, C
FROM
TABLE
WHERE
A > :A
OR
B>:B
In SQL 6.4A, a single slice of the index would be scanned, defined by the
predicate A
>
:A, the only matching column as it is a range predicate. Column
B would be checked during this single scan due to index screening, the index
entry being ignored if the predicate B
>
:B wasn't satisfied.
In SQL 6.4B, because of the OR operator, the DBMS cannot read
this slice
alone
because even if the predicate A
>
:A isn't satisfied, the predicate B
>
:B
may well be. The only feasible alternatives would be a full index scan, a full table
scan, and,
if there is another index starting with column B
, multiple index access.
In this case we cannot blame the optimizer. Sometimes a cursor with a
complex WHERE clause may have to be split into several cursors with simple
WHERE clauses in order to avoid problems like this. In general, this type of
predicate is
too difficult for the optimizer if a row cannot be rejected when that
predicate is false without checking other predicates
. In SQL 6.4B, a row cannot
be rejected just because predicate A
>
:A is false, nor because predicate B
>
:B
is false. In SQL 6.4A a row
can be
rejected if predicate A
>
:A is false without
>
needing to check predicate B
:B.
Such a predicate is called a
non-Boolean term
or
non-BT
. If there are only
AND operators in a WHERE clause as in SQL 6.4A, all simple predicates are
BT (Boolean term) and hence “good” predicates because a row can be rejected
if any one of the simple predicates is evaluated false.
Search WWH ::
Custom Search