Re: selecting top performers

stephen O'D wrote:
> I don't see anything wrong with using rownum to pull out the rows you
> want, whether its the top N, middle N or last N. Its always going to
> be faster than processing it row by row in a cursor.
>
> > I direct the OP to
> > http://www.orafaq.com/faq/Server_Utilities/SQL/faq24.htm
>
> I tried out the rank() method and it used more CPU than the rownum
> method:-

[]
>
> Which suggests the rownum method is better ...

Faster, yes, Better? that may be different.

It's just that ROWNUM is one of my pet peeves, along with SELECT DISTINCT. If you really need the performance, fine. However

 Many folks try to us the ROWNUM kludge for paging query results on the display (both terminals and web pages), which IMHO is a BAD design. (to get all pages of 10 rows each the rownum query must be run once for each page so the speed difference is diminished. And I would question the need for speed if the query for top-N is just generating a printed report.

The DISTINCT option often suggests to me that the conditions of the where clause are really incomplete. In a code review, I would reject a SELECT DISTINCT unless there was clear justification in the comments.

SO that's what I see wrong with ROWNUM.

   Ed Received on Wed Aug 10 2005 - 12:51:33 CDT