问答中心分类: ORACLE排序后如何限制 Oracle 查询返回的行数?
0
匿名用户 提问 1月 前

有没有办法制作一个Oracle查询的行为就像它包含一个 MySQLlimit条款?
在 MySQL 中,我可以这样做:

select * 
from sometable
order by name
limit 20,10

获得第 21 到第 30 行(跳过前 20 行,给出接下来的 10 行)。行在之后被选中order by,所以它实际上是按字母顺序从第 20 个名字开始的。
在 Oracle 中,人们唯一提到的是rownum伪列,但已评估 order by,这意味着:

select * 
from sometable
where rownum <= 10
order by name

将返回一组按名称排序的随机十行,这通常不是我想要的。它也不允许指定偏移量。

dalle 回复 1月 前

在 SQL:2008 中标准化。

dalle 回复 1月 前

看起来 offset 和 row_count 在您的第一个 MySQL 示例中是相反的。该查询将选择第 11 到 30 行,而不是 21 到 30 行。

dalle 回复 1月 前

Tom Kyte 宣布了 Oracle 12c 的限制…

dalle 回复 1月 前

认为它是题外话,但当您需要从数据集中间提取数据时,我几乎无法想象现实生活中的用例。不开玩笑。也许我只是一个幸运的人,还没有遇到这样的要求。如果有人可以根据实际经验举一个例子……

dalle 回复 1月 前

获取结果集中的下一页?

dalle 回复 1月 前

@YaroslavShabalin 特别是,分页搜索使用这种模式全部时间。几乎所有具有任何类型搜索功能的应用程序都会使用它。另一个用例是仅加载长列表或表格客户端的一部分,并为用户提供扩展选项。

dalle 回复 1月 前

@MathieuLongtin @jpmc26 在谷歌中,您最初是从第 10 页开始吗?当您可以执行一次,打开光标并通过加载数据集的某些部分进行导航时,为什么每次加载下一页时都需要执行查询?是的,如果结果集很大,您必须处理内存管理问题,但要避免可能的不一致。考虑到结果集中的行数改变,每次你用上面重新查询limit 20,10你可以得到不同的结果。

dalle 回复 1月 前

@YaroslavShabalin 除非基础数据因ORDER BY.这就是首先订购的全部意义所在。如果基础数据发生变化并且您的结果集因此而变化,那么为什么不向用户显示更新的结果而不是过时的信息呢?此外,状态管理是一个需要尽可能避免的瘟疫。这是复杂性和错误的持续来源;这就是为什么函数式变得如此受欢迎的原因。你什么时候知道使内存中的整个结果集过期?在 Web 中,您无法知道用户何时离开。

dalle 回复 1月 前

@jpmc26 好的,我明白你的意思。现在它是有道理的。

dalle 回复 1月 前

@YaroslavShabalin – 在多用户场景中打开游标并长时间保持打开状态是将关系数据库捆绑在一起的好方法。在单用户数据库中,这可能无关紧要,但是当您有数百个同时连接时,这很可能会导致问题。关系数据库通常被设计为具有相对较短事务持续时间的事务数据库。 YMMV。分享和享受。

dalle 回复 1月 前

也可以看看使用 Oracle 进行分页

12 Answers
0
sampathsris 回答 1月 前

从 Oracle 12c R1 (12.1) 开始,有一个行限制子句.它不使用熟悉的LIMIT语法,但它可以通过更多选项更好地完成工作。你可以找到完整的语法在这里. (另请阅读有关其在 Oracle 内部如何工作的更多信息这个答案)。
要回答原始问题,请输入以下查询:

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(对于早期的Oracle版本,请参考此问题中的其他答案)

例子:
以下示例引用自链接页面,希望防止链接失效。
设置

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

桌子上有什么?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

抢先N

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

抢先N行,如果Nth行有关系,获取所有被绑定的行

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

最佳x行数百分比

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

使用偏移量,对分页非常有用

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

您可以将偏移量与百分比结合起来

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.
Lukasz Szozda 回复 1月 前

只是为了扩展:OFFSET FETCH语法是语法糖。细节

PAA 回复 1月 前

我们如何获得 Oracle 11G 中的 LIMIT 和 OFFSET ?

sampathsris 回复 1月 前

@Pra_A 11G 中没有原生支持LIMIT/OFFSET.如果您检查其他答案,他们都以一种或其他方式实际实施了限制和偏移。

0
zeldi 回答 1月 前

我对以下方法进行了一些性能测试:
阿斯通

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

分析型

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

空头替代

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

结果
表有 1000 万条记录,排序在未索引的日期时间行上:

  • 解释计划显示所有三个选择的值相同 (323168)
  • 但获胜者是 AskTom(分析紧随其后)

选择前 10 行需要:

  • AskTom:28-30 秒
  • 分析:33-37 秒
  • 短替代:110-140 秒

选择 100,000 到 100,010 之间的行:

  • AskTom:60 秒
  • 分析:100 秒

选择 9,000,000 到 9,000,010 之间的行:

  • AskTom:130 秒
  • 分析:150 秒
Mathieu Longtin 回复 1月 前

不错的作品。您是否尝试过使用 between 而不是 >= 和 <= 的简短替代方法?

wweicker 回复 1月 前

@MathieuLongtinBETWEEN只是一个简写>= AND <=(stackoverflow.com/questions/4809083/between-clause-versus-and)

Leigh Riffel 回复 1月 前

zeldi – 这是哪个版本的? Oracle 在 11.1 中改进了分析性能。和 11.2。

zeldi 回复 1月 前

@Leigh Riffel 这是 10.2.0.5;有一天我可能会花时间检查 11i 版本。

Jon Heller 回复 1月 前

我进行了一些快速测试,并得到了 12c 的类似结果。新的offset语法具有与分析方法相同的计划和性能。

shinzou 回复 1月 前

myrow未定义

Avi E. Koenig 回复 1月 前

作为一个使用 10c 的 Oracle 的新手,我发现它没有像其他 sql 那样在本地实现

0
Leigh Riffel 回答 1月 前

只有一个嵌套查询的分析解决方案:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank()可以代替Row_Number()但如果 name 有重复值,则返回的记录可能比您预期的要多。

Dave Costa 回复 1月 前

我喜欢分析。您可能想澄清 Rank() 和 Row_Number() 之间的行为差​​异。

Leigh Riffel 回复 1月 前

确实,不知道为什么我不考虑重复。因此,在这种情况下,如果名称有重复值,那么 RANK 可能会提供比您预期更多的记录,因此您应该使用 Row_Number。

Paul Maxwell 回复 1月 前

如果提到rank()同样值得注意的是dense_rank()这对于输出控制可能更有用,因为后者不会“跳过”数字,而rank()能够。无论如何对于这个问题row_number()最适合。另一个不是这种技术适用于任何支持上述功能的数据库。

0
Vlad Mihalcea 回答 1月 前

SQL 标准
由于版本 12c,Oracle 支持 SQL:2008 标准,该标准提供以下语法来限制 SQL 结果集:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g 及更早版本
在版本 12c 之前,要获取前 N 个记录,您必须使用派生表和ROWNUM伪列:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50
Ak777 回复 1月 前

我很好奇,Oracle 中从来没有一种语法支持使用“从 {TableName} 中选择前 N *”或类似的东西?

Vlad Mihalcea 回复 1月 前

@Ak777 不。那只是 SQL Server。

0
beldaz 回答 1月 前

在 Oracle 12c 上(参见行限制子句SQL 参考):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Mathieu Longtin 回复 1月 前

当然,到目前为止,他们必须使用与其他所有人完全不同的语法

beldaz 回复 1月 前

显然是在与所有其他供应商坐下来商定之后LIMIT在 SQL:2008 中,他们不得不从微软的书中吸取教训并打破标准。

beldaz 回复 1月 前

有趣的是,我最近听说最新的标准包括这种语法,所以可能是 Oracle 在实施之前先推出了它。可以说它比它更灵活LIMIT ... OFFSET

sampathsris 回复 1月 前

@Derek:是的,不遵守标准是令人遗憾的。但是 12cR1 中新引入的功能不仅仅是强大LIMIT n, m(见我的回答)。再说一次,甲骨文应该已经实施LIMIT n, m作为语法糖,因为它等价于OFFSET n ROWS FETCH NEXT m ROWS ONLY.

beldaz 回复 1月 前

@Derek:实际上,我只是在 PostgreSQL 手册中注意到了这句话postgresql.org/docs/9.0/static/sql-select.html#AEN69535“子句 LIMIT 和 OFFSET 是 PostgreSQL 特定的语法,也被 MySQL 使用。SQL:2008 标准引入了子句 OFFSET … FETCH {FIRST|NEXT} … 用于相同的功能”。所以 LIMIT 从来都不是标准的一部分。

Wirone 回复 1月 前

@beldaz我发现你的链接坏了(因为锚点改变了?),正确的是postgresql.org/docs/9.0/static/sql-select.html#AEN69605(当然现在;-))

beldaz 回复 1月 前

@Krumia 谢谢。甚至可以将 http://www.postgresql.org/docs/current/static/sql-select.html#SQL-LIMIT 用于最新版本

trpt4him 回复 1月 前

抛开所有标准化争论不谈,如果您只需要适用于 Oracle 的东西,截至 2014 年,这是现在所有答案中最好的,恕我直言。 +1