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. Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfgang Breitling Sent: Friday, July 16, 2004 2:34 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: 9.2.0.5, views, queries, and pushing predicates..... If we follow the argument that was brought forward in the to_number thread, then the predicate must never be pushed into the view because the writer of the sql obviously wanted the view to be "materialized" before applying the outer predicate. At 12:33 PM 7/16/2004, you wrote: >My test case had the following SQL to demonstrate the problem: select * >from xan_view where doc_id = 38943105; and with the patch applied, this >did the right thing. However, in the real world, we use little things >called BIND VARIABLES. > >If you re-write the above query and execute: > >variable b1 number; >exec :b1:=38943105; >select * from xan_view where doc_id = :b1; > >IT FAILS!!! ARGH! I mean, I never considered that the fix would only >address the case where a literal is specified! So, it took two weeks >to get the patch, and I'm right back where I started from!!! Regards Wolfgang Breitling Centrex Consulting Corporation http://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 ----------------------------------------------------------------- -- Attached file included as plaintext by Ecartis -- -- Desc: Signature The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ---------------------------------------------------------------- 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 -----------------------------------------------------------------