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

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 16 Jul 2004 15:47:05 -0600

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2004