Oracle rownum < [n] 的替代方案是什么？了解一下？

Question

据我所知，rownum是在查询结果集完成后应用的。这意味着如果我想使用rownum来限制结果，它仍将首先查询所有内容。我的用户表格有超过十万条记录。我正在开发一个站点来搜索这个表并返回结果。不幸的是，请求者希望我能够仅搜索姓氏。

想象一下可能会返回的"Jones","Whites","Browns"等数量。我想最多只返回不超过200条记录，是否有更好的方法来实现这一点而不使用rownum？我的对rownum应用时机的理解是否正确？

- Eric Harms

Answer 1

SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   lastname = 'Jones'
        ORDER BY
                id
        )
WHERE   rownum <= 200

或者

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) rn
        FROM    mytable
        WHERE   lastname = 'Jones'
        )
WHERE   rn <= 200

在9i版本中后者速度较慢，但在10g+版本中表现相同。

我的理解是，在查询完成后对整个结果集应用rownum。

不是这样的。当满足WHERE 子句的每条记录被获取（但在排序之前），rownum 就会被应用。

实际上，在此处需要嵌套查询，因为ROWNUM 在ORDER BY之前被评估。

无论是ROWNUM还是ROW_NUMBER(), 都会受到优化。如果你在(lastname, id)上建立了索引，查询将使用该索引，并在返回第200条记录后停止（可以在计划中看到COUNT(STOPKEY)）。

此外，ROWNUM和分页有一个常见的陷阱。 这个查询：

SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   lastname = 'Jones'
        ORDER BY
                id
        )
WHERE   rownum BETWEEN 201 AND 400

这个查询语句将永远不会返回任何结果，因为 ROWNUM 已经是 WHERE 条件的一部分。数据库引擎无法返回第一行，因为它将具有 ROWNUM = 1，而这不满足 WHERE 子句的条件。

要解决这个问题，你需要再加一层嵌套查询：

SELECT  *
FROM    (
        SELECT  q.*, ROWNUM AS rn
        FROM    (
                SELECT  *
                FROM    mytable
                WHERE   lastname = 'Jones'
                ORDER BY
                        id
                ) q
        )
WHERE   rn BETWEEN 201 AND 400

这也将被优化为一个 COUNT(STOPKEY)。

- Quassnoi