Re: Why does select max(..) sort?

True, and in 8.1.7 (ish) the full path when an index can be used optimally is:

SORT (AGGREGATE)
  FIRST ROW
    INDEX (RANGE SCAN (MIN/MAX)) OF MM_PK(UNIQUE) So oracle is clearly quite clever about doing it, whatever you read into the SORT() line.

Simple test - run the query with

    set autotrace traceonly statistics
and see how many rows are reported
as sorted - that tells you if Oracle really had to do a sort.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept
 Australia August
 Malaysia September
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html

Andy wrote in message ...


>Rene

>

>SORT(AGGREGATE) is the one SORT item that appears in the EXPLAIN PLAN

output


>that does not imply a sort.  If you notice in the Tuning Manual of the

>Oracle documentation, all the other SORT(actions) perform "Operation

sorting


>a set of rows .." whereas SORT(AGGREGATE) is described as "Retrieval of a

>single row that is the result of applying a group function to a group of

>selected rows".  It is highly likely, given the maturity of Oracle and the

>typical expense of sort operations, that Oracle does not actually sort the

>data to determine the max() value even when, as in your example, it

requires


>a full table scan.  When the column is indexed or is the leading edge of a

>multi column index, Oracle will still report a SORT(AGGREGATE) operation.

>

>Andy

>

>"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message

>news:Xns9253D63983B5gnuegischgnueg_at_130.133.1.4...

>> Hello

>>

>> I am wondering why Oracle sorts a table in order to do a select max (..):

>>

>> explain plan for select max(n) from test_for_max;

>>

>> ->

>>

>> SELECT STATEMENT ()

>>  SORT (AGGREGATE)

>>   TABLE ACCESS (FULL)          TEST_FOR_MAX

>>

>>

>> It seems to me that the sort is unnecessary because while a table

>> scan is performed on test_for_max, Oracle could compare an internal

>> variable max_so_far with the current value of n and set max_so_far equal

>> n if n is larger.

>>

>> pseudo code

>>

>> max_so_far = -INFINITE;

>> while (row* r=get_a_row_from_table(test_for_max)) {

>>   if (r->n > max_so_far) max_so_far=n;

>> }

>> return max_so_far;

>>

>>

>> TIA

>>

>> Rene

>>

>>

>>

>> --

>> Latest article on my HP (as of 14.07.2002):

>>   Transforming each character in a string with perl

>>   http://www.adp-gmbh.ch/perl/each_char.html

>

>