Re: Strange behaviour of function based index

Hello Dave,

you gave me the hint to solve the problem. The reason was the contents of the date column. The TRUNCed date was allover the same, due to an error in the conversion from an Interbase DB! So the computed statistics led to using the full table scan.

Many Thanks
  Gerrit

davidr212000_at_yahoo.com (Dave) wrote in message news:<5e092a4e.0404060714.5dc96e94_at_posting.google.com>...
> 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 Wed Apr 07 2004 - 01:59:49 CDT