Re: select max

Hi.

 Did you try using descending index in 8i?  I think it supposed to improve such queries.  Anyway, isn't it better to write a function that performs  OPEN cursor ( either using descending index or INDEX_DESC hint );  FETCH cursor ( once only ) ; and
 CLOSE cursor;

 Michael.

In article <933348556.5618.2.nnrp-03.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Folowing the note you sent me, I've done
> a couple of extra tests, and indeed the
>
> select max(indexed column) from table;
>
> does exactly the short circuit that one would
> hope for - the ambiguity is even eliminated in the
> 8.1.5 execution plan with the line:
>
> INDEX FULL SCAN (MIN/MAX)
>
> The exampe where the 'short cut' does not take
> place is in:
> select max(indexed column)
> from table
> where indexed_column <= {constant}
>
> This type of query scans the index from the
> bottom up, and then sorts all the rows found,
> when it would be more efficient to enter the index
> at {constant} and work downwards - ...
>
> *(I've added it to my enhancement request list ;)
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Jonathan Lewis wrote in message
> <933337782.17446.0.nnrp-13.9e984b29_at_news.demon.co.uk>...
> >
> >The interesting thing about that is that Oracle
> >will still do a FULL SCAN on the index, and
> >sort all the key values to find the max().
> >
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sat Jul 31 1999 - 07:40:08 CDT