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

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 10 Jan 2007 09:29:37 -0600
Message-ID: <7b8774110701100729n3ee05b06l20dced8e24742efb@mail.gmail.com>

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

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

Received on Wed Jan 10 2007 - 09:29:37 CST