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@xxxxxxxxx> 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
