Count fun

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 Received on Thu Apr 27 2006 - 07:08:17 CDT