first n rows...

Hi,
I need to return first n rows from a query using oracle. I know that one (hopefully not the only ;) way is to add rownum<n in my where clause, e.g.

    select * from items where rownum<1000.

I have a problem because I want to fetch the first 1000 rows from a table ORDERED by NAME (for example).
The only way I know how to do this is :

    SELECT * FROM (SELECT * FROM ITEMS ORDER BY NAME) WHERE ROWNUM<=1000;

This is very, VERY slow, because the inner select is executed on all records and the outer select returns the 1000 rows. if I do

    SELECT * FROM ITEMS WHERE ROWNUM<=1000 ORDER BY NAME; I'll get the first 1000 rows from a table ordered by name, which is of no use to me!

I've tested similar query on 500 records on different databases on the same server, and the results are:
ORACLE 8.1 :
    SELECT * FROM (SELECT * FROM ITEMS ORDER BY NAME) WHERE ROWNUM<=500; 2.19 sec

MS SQL SERVER 2000 :
    SELECT TOP 500 FROM ITEMS ORDER BY NAME 0.7 sec

MYSQL 3.23.28 GAMMA :
    SELECT FROM ITEMS ORDER BY NAME LIMIT 500; 0.29 sec

All the databases have an index on NAME and contain the same data and datatypes.

Is there a way to do something like LIMIT or TOP statement in ORACLE?

thanx, igor Received on Tue Nov 20 2001 - 11:06:20 CET