Bdtjtk

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 ?

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?

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

The following is what I typically use for top-n type queries (pagination query in this case):

select * from (

  select a.*, rownum r

  from (

    select *

    from your_table

    where ...

    order by ...

  ) a

  where rownum <= :upperBound

)

where r >= :lowerBound;

I usually use an indexed column to sort in inner query, and the use of rownum means Oracle can use the count(stopkey) optimization. So, not necessarily going to do full table scan:

create table t3 as select * from all_objects;

alter table t3 add constraint t_pk primary key(object_id);

analyze table t3 compute statistics;



delete from plan_table;

commit;

explain plan for

select * from (

  select a.*, rownum r

  from (

    select object_id, object_name

    from t3

    order by object_id

  ) a

  where rownum <= 2000

)

where r >= 1;



select operation, options, object_name, id, parent_id, position, cost, cardinality, other_tag, optimizer

from plan_table

order by id;

You'll find Oracle does a full index scan using t_pk. Also note the use of stopkey option.

Hope that explains my answer ;)

Order by may become heavy operation if you have lots of data. Take a look at your execution plan. If the data is not real time you could create a material view on these kind of selects...

In older versions of ORACLE (8.0) you don't have the possibility to use ORDER BY clause in subquery.
So, only for those of us who yet use some ancient versions, there is another way to deal with: The magic of UNION operator.
UNION will sort the records by columns in the query:

Example:

SELECT * FROM

(SELECT EMP_NO, EMP_NAME FROM EMP_TABLE

UNION

SELECT 99999999999,'' FROM DUAL)

WHERE ROWNUM<=5

where 99999999999 is bigger then all values in EMP_NO;

Or, if you want to select TOP 5 salary employees with the highest 5 salaries:

SELECT EMP_NO, EMP_NAME, 99999999999999-TMP_EMP_SAL

FROM

(SELECT 99999999999999-EMP_SAL TMP_EMP_SAL, EMP_NO, EMP_NAME 

FROM EMP_TABLE

UNION

SELECT 99999999999999,0,'' FROM DUAL)

WHERE ROWNUM<=5;

Regards,

Virgil Ionescu