RE: 9.2.0.5, views, queries, and pushing predicates.....

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 16 Jul 2004 15:47:05 -0600
Message-Id: <6.1.0.6.2.20040716153751.02e0f3c0@pop.centrexcc.com>

Actually, the pushing of the predicate - and the subsequent reordering - is precisely what causes the problem

Stephen wanted the predicate in the inline view, which guarantees that only rows qualify where the column value can be converted to a number - be evaluated BEFORE applying the predicate outside the inline view, which does a numeric comparison. That is why he created the inline view, thinking that would impose an ordering.

What the optimizer did was push the comparsion predicate inside the view ( or unnest the view, end result is the same ) and then had a simple sql with two predicates and decided to evaluate the comparison BEFORE the one the filters non-numeric values - and that caused to "not a numeric value" error.

Why the optimizer decides not to push the predicate is a totally different issue. It is allowed to do the push but for some reason didn't and Mark thinks it ought to because the resulting plan is better than "materializing" the view before applying the predicate.

At 01:59 PM 7/16/2004, you wrote:
>I confess, I haven't read the to_number thread completely. But I
>thought, to_number thread was(is?) about view merging, not pushing the
>predicates to the view. View doesn't need to be merged, but the
>predicates can be pushed, right ? Or Are you mocking something( which I
>didn't get )?
>Looking at the Mark's case, I think, it is more of a CBO decision not to
>push the predicates due to bind variables. Usage of bind variables
>introduces few assumptions in the cardinality calculations and that
>might disallow CBO from pushing the predicates. Mark, can you please
>look at the explain plan and see whether the view is being merged in any
>of this case ? Further, you might want to review the 10053 trace output
>for these two conditions and my guess is that cardinality calculations
>are causing this issue.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 16 2004 - 16:51:14 CDT