I had the same thing with null values and function-based
indexes. Oracle does not like it.
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@gmail.com>
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
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer