Oracle TOP N个有序行 [英] Oracle TOP N ordered rows

问题描述

我想从Oracle表中获取按日期排序的前N行.

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

执行此操作的常用方法，此解决方案针对我在SO/google上可以找到的每个问题返回.

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

在我的情况下，此解决方案不可行，因为myTable包含大量行 导致Oracle花太多时间才能返回子查询中的所有行.

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.

问题是，有没有办法限制子查询中返回的ORDERED行的数量?

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

推荐答案

您的推断是Oracle必须在子查询中返回所有行，然后才能滤除第一个N是错误的.它会开始从子查询中获取行，并在返回N行时停止.

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.

话虽如此，可能是Oracle需要从表中选择所有行并对它们进行排序，然后才能开始返回它们.但是，如果在ORDER BY子句中使用的列上有一个索引，则可能不会.

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与其他任何DBMS处于同一位置:如果您有一个大表，而排序时该列上没有索引，那么它如何在不先获取所有行并进行排序的情况下，如何知道哪些行是前N位他们吗?

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?

这篇关于Oracle TOP N个有序行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！