Regarding function based indexes.

Is this feature documented or am I hitting a bug, see bellow:


Example:

Create table T (n number)

Insert into T values (1)

--This is Function-based index:

create index I on T ('')

begin dbms_stats.gather_table_stats('SCOTT', 'T', cascade=>true); end;

And then:

 select count(*) from T

Result: 0 !

P.S.

This is probably what Oracle is trying to communicate to the audience in
its admin quide:

"The query must be guaranteed not to need any NULL values from the
indexed expression, since NULL values are not stored in indexes."

The fun is that at least a few not null index expression values remedies
the situation...



Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l