Database Reference
In-Depth Information
Are you surprised at the predicate that's left? If you think about it, you'll see that
n1 = 1
is enough to satisfy the
query, whereas
n2 = 2
requires that
n1 = 1
also be looked at.
Or Expansion
The purpose of
or expansion
is to transform a query with a
WHERE
clause containing disjunctive predicates into
a compound query that uses one or several
UNION ALL
set operators. In general, each disjunctive predicate is
transformed into a component query. This is a cost-based query transformation that is applied, most of the time, to
enable additional index access paths. In fact, as Chapter 13 explains, disjunctive predicates and indexes don't always
go well together. Also note that this query transformation supports function-based indexes as of version 11.2.0.2 only.
■
even though or expansion is a cost-based query transformation, the query optimizer checks some heuristics
before attempting it. If the query transformation is disallowed, an execution plan with a lower cost can be missed.
Note
In the following example, based on the
or_expansion.sql
script, notice that the
WHERE
clause contains two
disjunctive predicates. Because of them, the query optimizer evaluates whether the cost of a single access based on a
table scan is higher than the cost of two distinct accesses based on index scans:
SELECT pad
FROM t
WHERE n1 = 1 OR n2 = 2
If the cost of the two index scans is lower, or expansion produces the following query. Notice that the
lnnvl(n1 = 1)
predicate is added to avoid duplicates. The
lnnvl
function returns
TRUE
when the condition passed as a parameter is
FALSE
or
NULL
. Hence, a row is returned by the second component query only when the first component query didn't
already return it:
SELECT pad
FROM t
WHERE n1 = 1
UNION ALL
SELECT pad
FROM t
WHERE n2 = 2 AND lnnvl(n1 = 1)
Some disjunctive predicates can never be sensibly transformed with or expansions. The following query shows
an example. All predicates reference the column named
n1
, enabling the
WHERE
clause to be processed like an
IN
condition:
SELECT *
FROM t
WHERE n1 = 1 OR n1 = 2 OR n1 = 3 OR n1 = 4