Re: Strange behaviour of function based index

g.entelmann_at_t-online.de wrote in message news:<390eb482.0404060248.73c25dd3_at_posting.google.com>...

The Count query can be satisfied with the index alone.

What is your query without the count?
select * from Footable where Trunc(aDate) = Trunc(Sysdate) ???

If so, with the select *....maybe Oracle determined that a significant amount of rows would need to be read to return the results anyway, so why not just scan the table.

Even if your query is....
select aDate from Footable where Trunc(aDate) = Trunc(Sysdate)

This query cannot be totally answered by the index since the index is storing Trunc(aDate) and you are selecting "aDate". So again, Oracle may prefer table scan based on the current statistics gathered for the table and index.

If I am right, then the following query would be satisfied by the index alone....
select Trunc(aDate) from Footable where Trunc(aDate) = Trunc(Sysdate)

Dave

> Hello,
>
> I encountered some strange behaviour of a function based index.
>
> The Index: Create Index FooIndex on Footable(Trunc(aDate));
>
> Executing the following query uses the index:
>
> select Count(aDate) from Footable where Trunc(aDate) = Trunc(Sysdate);
>
> But when I use the query without the 'Count', it uses a full table scan.
>
> Can anyone explain this ??
>
>
> Gerrit
Received on Tue Apr 06 2004 - 10:14:26 CDT