Re: Anyone have more information about metalink note 362585.1 or bug 4622729?

We did apply it and it did not resolve our problem, hence the new SR. Kinda makes me wonder what the one-off actually does. =)

On 1/10/07, Ghassan Salem <salem.ghassan_at_gmail.com> wrote:
>
> From what I see, it's not fixed in 10.2.0.2, but .3, there is a one-off on
> top of .2 that you can apply.
> rgds
>
> On 1/10/07, Charles Schultz <sacrophyte_at_gmail.com> wrote:
> >
> > We applied the patch for 10.2.0.2, but we are still seeing the same
> > problem as documented in the metalink note. We have already raised the issue
> > with Oracle Support, but since I expect a less-than-complete answer from
> > them about the internal details, I was hoping to pose a few questions here.
> >
> > Given the scenario in the note, exactly why are the filter conditions
> > different for the two select statements? Which filter, specifically, is
> > "NULL IS NOT NULL"? And why would cursor_sharing play a role in the filter
> > operation? The more details the better, I am eager to learn. *grin*
> >
> > create table t1 (a number not null);
> > create table t2 (b number,c varchar2(1));
> > insert into t1 values (1);
> > commit;
> >
> > set autotrace on explain
> >
> > SQL> select a from t1 where a not in (select b from t2 where b is not
> > null and c ='');
> > no rows selected
> >
> > SQL> select a from t1 where a not in (select b from t2 where b is not
> > null and c =' ');
> >
> > A
> > ----------
> > 1
> > SQL> select a from t1 where a not in (select b from t2 where b is not
> > null and c ='');
> > ...
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> >
> > 1 - filter(NULL IS NOT NULL)
> > 2 - access("A"="B")
> > 4 - filter("B" IS NOT NULL)
> >
> > SQL> select a from t1 where a not in (select b from t2 where b is not
> > null and c =' ');
> > ...
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> >
> > 1 - access("A"="B")
> > 3 - filter("B" IS NOT NULL AND "C"=' ')
> >
> > --
> > Charles Schultz
>
>
>

-- 
Charles Schultz

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