sql - Oracle rownum < [n] 替代方案？对此事的认知？

Question

根据我的理解，rownum 是在查询完后应用于整个结果集的。这意味着如果我想使用 rownum 限制结果，它首先仍会查询所有内容。我有一个用户表，有超过十万条记录。我也在开发一个网站，搜索这个表带回一个结果集。不幸的是，请求者希望我能够只搜索姓氏。

想象一下可能回来的“琼斯”、“白人”、“棕色人”。我想带回不超过 200 条记录，有没有比使用 rownum 更好的方法来做到这一点？我对何时应用 rownum 的理解正确吗？

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+ 中的工作原理相同.

From my understanding, rownum is applied to the entire result set after it has been queried

号 rownum只要满足 WHERE 的每个记录就应用子句被提取(但在它们被排序之前)。

实际上，这里需要嵌套查询，因为 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)也。