Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Wrong Results - Bad Execution Plan
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
x.2=y.2 AND y.3=z.3 AND a.1 = (SELECT /*+ qb_name(min) */ MIN() FROM x as w .) AND -importantclause
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 )
Points to notice include
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 16 2007 - 14:42:31 CST