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

"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

a max doesn't necessarily imply a full table scan. Oracle has implemented a mechanism which works regardless the access path for the underlying table(s) involved.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address