Re: Finding the 10 smallest values in a column

Jeff Brown wrote:
>
> How about (I didn't test this, but I've used similar):
>
> Select VAL
> From TABLE
> Where ROWNUM < 11
> Order by VAL Asc
> ;
>
> If you want values only once, make that "Select Distinct VAL".
>
> Jeff Brown
> JF.Brown_at_pnl.gov

For smal tables the solution is fine but for bigger tables I think you get a performance hit.
To get a good performance this is not the way to go. On a similar subject Oracle gave the following answer.

cursor X
is select <whatever>
order by yourvalue decending
begin

	open X
	for i in 1..10
	loop
		fetch X into r
		cache or process the data
	end loop

In this manner the ten highest yourvalue's become available. Tuning of this query by the statistical optimizer (Oracle) on statistical information and indexes should give a good performance. An index over yourvalue is assumed. Only the ten highest yourvalue's are accessed. No sorting, no accessing of more than 10 indexes (1 or 2 blocks) and 10 rows is done.

-- 
E-mail: 
+-----------------------------------------------------------------------+
| stef ten bras, HISCOM afdeling Systeem Ontwikkeling         __   o  ~
|
| Schipholweg 97, Postbus 901, 2300 AX Leiden, Nederland      _ _/[._  
|
| Tel. +31 71 5256758,      Email stbras_at_hiscom.nl         --  (_)>(_) 
|
+-----------------------------------------------------------------------+