It appears that Oracle is taking your hint quite seriously.
As there is no data in idx2 due to the only value in the id2 column being null, Oracle is not returning any rows.
Take idx2 out of the hint, and you get data.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist
On 7/13/06, Gints Plivna <gints.plivna@(protected)> wrote: > > ... > SQL> create index idx2 on blahh (id2); > > Index created. > > SQL> insert into blahh values (1, null); > > 1 row created. > > ... > > SQL> select 'x' from dual where exists ( > 2 select /*+ index_ffs (blahh idx1 idx2) */ 'z' from blahh where > 3 (id1 = 1 and id2 is null) or (id1 is null and id2 = 1)) > 4 / > > no rows selected > > >
It appears that Oracle is taking your hint quite seriously.<br><br>As there is no data in idx2 due to the only value in the<br>id2 column being null, Oracle is not returning any rows.<br><br>Take idx2 out of the hint, and you get data. <br><br>-- <br>Jared Still<br>Certifiable Oracle DBA and Part Time Perl Evangelist<br> <br><br><div><span class="gmail_quote">On 7/13/06, <b class="gmail_sendername" >Gints Plivna</b> <<a href="mailto:gints.plivna@(protected)">gints.plivna@(protected) .com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> ...<br>SQL> create index idx2 on blahh (id2);<br><br>Index created.<br><br >SQL> insert into blahh values (1, null);<br><br>1 row created.<br><br>...<br ><br>SQL> select 'x' from dual where exists (<br> 2 select /*+ index_ffs (blahh idx1 idx2) */ 'z' from blahh where <br> 3 (id1 = 1 and id2 is null) or (id1 is null and id2 = 1))<br> 4 /<br><br>no rows selected<br><br ><br></blockquote></div><br>
