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

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
Received on Tue Jul 23 2002 - 03:38:04 CDT