Re: select max

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

I had hoped for a cunning short-cut by the time 8.1 came out. But this means it is still necessary to play with (index_desc) hints when data sets get large.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Thomas Kyte wrote in message <37b18c21.174513026_at_newshost.us.oracle.com>...
>A copy of this was sent to "Marco Toccafondi" <mt_at_multidatagroup.it>
>(if that email address didn't require changing)
>On Fri, 30 Jul 1999 09:37:50 +0200, you wrote:
>
>it uses an index in most cases when available and finds the answer pretty
>quickly. You can test simple things like this with 'autotrace' in sqlplus.
>consider:
>
>Indexes on tkyte.empnos
>
>Index Is
>Name Unique Indexed Columns
>------------------------------ ------ -----------------------------------
>SYS_C0048272 Yes EMPNO

>
>SQL> set autotrace on
>SQL> select max(empno) from empnos;
>
>MAX(EMPNO)
>-------------------------
>E00001000
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FULL SCAN) OF 'SYS_C0048272' (UNIQUE)
>
Received on Fri Jul 30 1999 - 07:24:18 CDT