Wrong Results - Bad Execution Plan
- From: "Henry Poras" <henry@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Nov 2007 15:42:31 -0500
Oracle can, on occasion, generate an execution plan which returns incorrect
results.
In our 10.2.0.3 database we have two SQL queries which should give identical
result sets, but don't. Outside of a filter which does not apply, the
queries are logically identical. They do, however, resolve to different
execution plans, one of which is correct and the other which is WRONG.
A simplified version of the SQL involve is as follows:
SELECT /*+ qb_name(outer) */ *
FROM (
SELECT /*+ qb_name(inner) */ a.1, z.x
FROM
x,
y,
z,
x as a,
x as b
WHERE
x.2=y.2 AND
y.3=z.3 AND
a.1 = (SELECT /*+ qb_name(min) */ MIN() FROM x as w .) AND -important
clause
b.1 = (SELECT /*+ qb_name(max) */ MAX() FROM x as q .) AND --important
clause
( NOT EXISTS (SELECT 'x' FROM x as l WHERE l.1<a.1 AND .) OR
(NOT EXISTS (SELECT 'x' FROM x as m WHERE m.1<a.1 AND .)AND
NOT EXISTS (SELECT 'x' FROM x as n WHERE .)
) ) AND
other filters
)
WHERE rownum <= 5000;
Points to notice include
* the number of records returned < 20. It is always less than
5000. This means that running "inner" should give the same result set as
running "outer".
* both the max and the min clause are equalities on the
underlying field x.1, but min is on the aliased table a, and max on the
alias b.
* the "inner" query block returns only a.1 to "outer", not b.1
The next step is to examine the two execution plans
To simplify this discussion I will use the following labels for query
blocks/result sets:
nested loops result from x,y,z joins "A"
min clause "B"
max clause "C"
nested clause "D or (E and F)"
other filters "G"
"INNER"
(A and G and ((E and F) and B and C)
concatenate
(A and G and ((D) and B and C)
"OUTER"
(
(A and G and ((E and F))
concatenate
(A and G and ((D))
)
and (B and C)
Since the max and min clauses, "B" and "C", are ANDed, it should not matter
if they are applied before or after the "concatenate".
It does matter, however, and here is why.
The final few steps of "INNER" are
SORT UNIQUE
CONCATENATION
FILTER (A and G and ((E and F) and B and C) --filter(."a"."1"= AND
"b"."1"= )
.(all the A and G and ((E and F) and B and C details)
FILTER (A and G and ((D) and B and C)
.(all the A and G and ((D) and B and C details)
The final few steps of "OUTER" are
COUNT STOPKEY --filter(ROWNUM<=5000)
FILTER --filter(("1"= AND "1"=)) {NOTE: where 1is a.1
passed to OUTER in the view}
VIEW
SORT UNIQUE
CONCATENATION
FILTER (A and G and ((E and F) ))
FILTER (A and G and ((D)) )
For the "OUTER" query, the execution plan should read
VIEW
FILTER
and not
FILTER
VIEW
This is the bug!!!
The final filter in "OUTER" filters on a.1 for the MAX clause, not b.1 as is
correct, since a.1 is the only value for 1 passed through the view.
Looking at the data for a single customer_id, the unconcatenated pieces
shows results consistent with the observed errors. This observation is
consistent with our reasoning, though it does not prove it.
Gotta love it!!
Henry
--
//www.freelists.org/webpage/oracle-l
Other related posts:
