sql - Oracle TOP N ordered rows

Question

I would like to get the top N rows from an Oracle table sorted by date.

The common way to do this, and this solution returns for every question I could find on SO/google.

Select *
from
(select * from
myTable 
ordered by Date desc)
where rownum < N

This solution is in my case impracticable because myTable contains an huge ammount of rows which would lead to Oracle taking too long to return all rows in the subquery.

Question is, is there a way to limit the number of ORDERED rows returned in the subquery ?

Answer 1

Your inference that Oracle must return all rows in the subquery before filtering out the first N is wrong. It will start fetching rows from the subquery, and stop when it has returned N rows.

Having said that, it may be that Oracle needs to select all rows from the table and sort them before it can start returning them. But if there were an index on the column being used in the ORDER BY clause, it might not.

Oracle is in the same position as any other DBMS: if you have a large table with no index on the column you are ordering by, how can it possibly know which rows are the top N without first getting all the rows and sorting them?