FIRST and LAST aggregate functions only require one sort

From: Tony Hasler <tony_at_aberdour.demon.co.uk>
Date: Wed, 21 Mar 2012 18:09:58 -0000
Message-ID: <BA8B8CB827554A248DD09FE4660121A6_at_VMGENERAL>



Hi Guys and Gals,  

This is my first post here so here goes:  

I came across the need to use the FIRST and LAST aggregate functions. This is, in fact, the first time that I have remembered of their existence when the need arose!  

When I looked at the execution plan I saw something I didn't expect. This is the basic statement:  

SELECT MIN (c1) KEEP (DENSE_RANK FIRST ORDER BY c2)

      ,MAX (c3) KEEP (DENSE_RANK LAST ORDER BY c4)

  FROM t1;  

Now the curious thing is that the execution plan shows only one sort! Indeed, the number of sorts remains the same no matter how may different ORDER BY columns I use.  

Does anybody have any idea how Oracle does it or whether it actually does some hidden sorts that it isn't telling us about?  

--Tony Hasler
 

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 21 2012 - 13:09:58 CDT

Original text of this message