主页 / dba / 问题 / 318325
Accepted
T.S.
Asked: 2022-10-18 15:16:34 +0800 CST

在内联视图中设置时Oracle是否保证最终输出行顺序

  • 772

此查询基于 2 个表，其中某些行PerfData1有多个子记录。行不是重复的，但有些id是重复的。我只需要获取其中的 1 行，这也是一个分页查询，它需要获取总数或现有项目和最终子集。

查询工作正常。但是，我不确定内联视图（内部ORDER BY）中设置的顺序是否会保留在最终输出中。到目前为止，是没有看到任何偏差。但我记得在 SQL Server 世界中这并不能保证，因此我需要 2 个ORDER BY子句。

    SELECT 
        All_Results_Table.*
    FROM
    (
        SELECT 
            Rownum_Table.*,
            COUNT(*) OVER() as sys___countAllFoundRecords,
            ROWNUM sys___outputRownum
        FROM 
        (
            SELECT 
                ROW_NUMBER() OVER(PARTITION BY PerfData1.id ORDER BY PerfData1.id) AS sys___multivalueRowSequence, -- This is OPTIONAL when we DON'T want dupe IDs
                ROW_NUMBER() OVER(order by 1) AS sys___innerRowNumber,
                PerfData1.id,
                PerfData1.c1 item,
                PerfData2.c1 subitem
            FROM 
                PerfData1 inner join 
                PerfData2 on PerfData1.id = PerfData2.fkId
            WHERE (1 = 1) 
            ORDER BY id, subitem desc --<<-- WILL THIS ORDER STAY in the FINAL OUTPUT 
        ) Rownum_Table
        WHERE sys___multivalueRowSequence = 1 -- This is OPTIONAL when we DON'T want dupe IDs
    ) All_Results_Table
    WHERE 
        sys___outputRownum BETWEEN 200 AND 230

ORDER BY上述SQL中设置的顺序会保留在最终输出中吗？

oracle

3 个回答

  1. Best Answer

    对于几乎任何 SQL 实现，都无法保证最终输出的顺序，除非您明确说明您希望该顺序是什么。

    在某些情况下，一个特定的其他可能非常有可能，但从不保证。SQL 是一种声明性语言，任何你不声明的东西，数据库都可以在以后的运行中自由地不重新定位。您现在可能在每个可以想象的测试中得到相同的顺序，但是稍后当数据增长和/改变平衡，或者有人更改受影响表的索引选择，或者一个或多个表成为视图，或者数据库升级到带有大量推文查询计划器的较新版本，处理数据的方式可能会发生重大变化，并且排序会丢失。

    如果您想保证复杂查询的最后一层或简单查询的特定顺序，您需要明确声明您希望ORDER BY在该级别使用子句进行排序。

    tl;博士：不。

    来自评论：

    在这种情况下，我可以使用一些提示来告诉 ORACLE 保持原始顺序吗？因为你知道，在这些多层查询中，我相信 Order by 会吃掉性能

    ORDER BY几乎总是¹有一些性能成本，但如果您需要按该顺序的数据，那么您需要按该顺序²因此需要付出代价。正如 Paul 所说，价格可能几乎为零：如果由于查询计划器决定的方式是获取您所要求的内容的最佳方法，那么无论如何都需要对数据进行排序，则无需重新排序，唯一的成本是规划者注意到这一点所花费的几分之一秒³。

    要对输出进行排序，ORDER BY必须适用于查询的最外层部分，在这种情况下移到ORDER BY id, subitem desc最后一个WHERE子句之后。没有其他方法可以保证最终排序，任何其他似乎有效的方法都依赖于可能随时更改的未定义行为。

    此外：关于ORDER BY在这样的子查询中；查询规划器可能会完全忽略它。一些数据库实际上会认为它的存在是语法错误。如果您依靠它对 的输出进行特定排序，ROW_NUMBER() OVER(order by 1)那么我将改为使用ROW_NUMBER() OVER(order by id, subitem desc).

    [1] 它不包括简单查询的情况，这些查询可以查找/扫描索引并且不引用任何不引用covered或不引用included该索引的内容，但如果您假设请求特定排序总是成本，那么您永远不会出错导致不愉快的惊喜的方式

    [2] 虽然有时让 DB 以任何旧顺序丢弃事物并在调用应用程序中排序是有益的，但将排序负载从 DB 移开是有益的，因为应用程序层通常最容易扩展

    [3] 或者它可能有两个看起来执行相同的计划选项，但是其中一个会自然地根据您想要的结果生成一种排序，而无需额外的工作，因此它会选择该计划而不是另一个。

    • 5

  2. order by只有在查询末尾有子句时才能保证对结果进行排序。Oracle 手册对此非常清楚。在“Oracle 数据库，SQL 语言参考，12c 第 1 版 (12.1)，E41329-25，2017 年 7 月”中，您可以阅读：

    order_by_clause

    使用 ORDER BY 子句对语句返回的行进行排序。如果没有 order_by_clause，则无法保证多次执行的同一查询将以相同的顺序检索行。

    对于某些关键字，提到它们不保证排序，例如这里

    group_by_clause

    GROUP BY 子句对行进行分组，但不保证结果集的顺序。要对分组进行排序，请使用 ORDER BY 子句。

    您还在评论中提到您为该UNION关键字找到了类似的注释。我认为原因是这些功能的传统实现需要对数据进行排序，因此结果已经排序。程序员通常依赖于此。但是现在有使用其他算法的实现，然后结果不再排序。

    • 2

  3. 最终输出中不会ORDER BY考虑子选择中的一个。是什么让你不这么认为？

    现在，这就是甲骨文，它可能会让你相信它会维持秩序，只是在你最意想不到的时候多次在背后捅你一刀。所以，不要。

    • 1

相关问题