Anyone have more information about metalink note 362585.1 or bug 4622729?
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Jan 2007 09:29:37 -0600
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
Other related posts:
