Re: Index usage ??

On 6 Feb 2002 23:20:01 -0800, andreas.prusch_at_oracle.com (Andreas) wrote:

>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.

If he has not Indexed the col he is selecting then there should be a table access right ....

>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

[Additions and Corrections Always Welcome.] Best Regards,
Ganesh R Received on Thu Feb 07 2002 - 02:21:21 CST