How to select first 'N' records from a database containing million records?

Question

I have an oracle database populated with million records. I am trying to write a SQL query that returns the first 'N" sorted records ( say 100 records) from the database based on certain condition.

SELECT * 
FROM myTable 
Where SIZE > 2000 
ORDER BY NAME DESC

Then programmatically select first N records.

The problem with this approach is :

• The query results into half million records and "ORDER BY NAME" causes all the records to be sorted on NAME in the descending order. This sorting is taking lot of time. (nearly 30-40 seconds. If I omit ORDER BY, it takes only 1 second).
• After the sort I am interested in only first N (100) records. So the sorting of complete records is not useful.

My questions are:

1. Is it possible to specify the 'N' in query itself? ( so that sort applies to only N records and query becomes faster).
2. Any better way in SQL to improve the query to sort only N elements and return in quick time.

Answer 1

If your purpose is to find 100 random rows and sort them afterwards then Lasse's solution is correct. If as I think you want the first 100 rows sorted by name while discarding the others you would build a query like this:

SELECT * 
  FROM (SELECT * 
          FROM myTable 
         WHERE SIZE > 2000 ORDER BY NAME DESC) 
 WHERE ROWNUM <= 100

The optimizer will understand that it is a TOP-N query and will be able to use an index on NAME. It won't have to sort the entire result set, it will just start at the end of the index and read it backwards and stop after 100 rows.

You could also add an hint to your original query to let the optimizer understand that you are interested in the first rows only. This will probably generate a similar access path:

SELECT /*+ FIRST_ROWS*/* FROM myTable WHERE SIZE > 2000 ORDER BY NAME DESC

Edit: just adding AND rownum <= 100 to the query won't work since in Oracle rownum is attributed before sorting : this is why you have to use a subquery. Without the subquery Oracle will select 100 random rows then sort them.