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
 
 
Search WWH ::




Custom Search