RE: Wrong Results - Bad Execution Plan

Update on this bug. It is a problem with 'PREDICATE PULLUP'. If I disable this optimizer option (_optimizer_filter_pred_pullup=false) the filter is no longer pulled out of the view. There are a number of bugs listed in metalink associated with this feature (just search on _optimizer_filter_pred_pullup).

Henry

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Henry Poras
> Sent: Friday, November 16, 2007 3:43 PM
> To: oracle-l_at_freelists.org
> Subject: {Spam?} 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.
>
>...
>...
>
> For the "OUTER" query, the execution plan should read
> VIEW
> FILTER
>
> and not
>
> FILTER
> VIEW
>
> This is the bug!!!
>
> Henry

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