Re: Index usage ??

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3C61B6C5.699E_at_yahoo.com>...
> Ralf wrote:
> >
> > I have the following statement:
> >
> > select max(value1) from table1
> >
> > There is an index "index1 on table1(value1)".
> >
> > The plan for the above statement shows me that the database uses an
> > full index scan and an table access by rowid to retrieve the value.
> > Why does it behave like this ?
> >
> > I tought the index should be sorted ? Then i would navigate at the end
> > of the index, get the value. I wouldn´t do any full index scan and
> > would not go to the table to get the value, because its in the index
> > already.
> >
> > Can someone explain, please ?
> >
> > Ralf
>
> version?
>
> later versions of oracle will/can/may show a 'INDEX (MIN/MAX)' in the
> explain plan which does exactly what you were anticipating.
>
> hth
> connor

Hi together,

the index is sorted binary. This may have influence, if you retrieve max values from linguistic sorted columns?

But the table access is still curious.
Can you post/email the execution plan, if you have a newer Oracle version? Additional constraints like not null constraints and new enhancements like function based indexs are also important.

Best regards,
Andreas Received on Thu Feb 07 2002 - 01:20:01 CST