Wrong Results - Bad Execution Plan

From: Henry Poras <henry_at_itasoftware.com>
Date: Fri, 16 Nov 2007 15:42:31 -0500
Message-ID: <00d301c82891$3585a8a0$ad07040a@itasoftware.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 16 2007 - 14:42:31 CST