按子查询排序和ROWNUM违背了关系哲学？

Question

Oracle的ROWNUM应用于ORDER BY之前。为了根据排序列放置ROWNUM，在所有文档和文本中提出了以下子查询。

select *
from (
  select *
  from table
  order by price
)
where rownum <= 7

这让我讨厌。据我所知，从输入到的表是关系型的，因此没有存储顺序，这意味着当从看到时，子查询中的顺序不受尊重。

我不记得确切的场景，但“ORDER BY在外部查询中没有影响”这一事实我已经阅读了不止一次。示例是内联子查询、INSERT的子查询、PARTITION子句的ORDER BY等。例如在

OVER（按姓名划分，按工资排序）

在外部查询中不会尊重工资顺序，如果我们希望在外部查询输出处对工资进行排序，则需要在外部查询中添加另一个ORDER BY。

大家对为什么这里不尊重关系属性，并且在子查询中存储顺序有一些见解？

Answer 1

sqlvogel的好答案是：

“据我所知，从中输入的表是关系型的”

不，对From的表输入不是关系的。它不是关系的，因为“表输入”是表，表不是关系。SQL中无数的怪癖和古怪最终都归结为一个简单的事实：SQL的核心建筑砖是表，而表不是关系。总结一下区别：

表可以包含重复行，关系不能包含重复行。（因此，SQL提供的是包代数，而不是关系代数。另一个结果是，SQL甚至不可能为其最基本的构建块html" target="_blank">定义等式比较！！！如果您可能需要处理重复行，您会如何比较表的等式？）

表可以包含未命名的列，关系不能。SELECT X Y From...因此，SQL被迫进入“按顺序位置的列标识”，因此，您会得到各种怪癖，例如在SELECT A、B from... UNION SELECT B、A From...

表可以包含重复的列名，关系不能。表中的A. ID和B. ID不是不同的列名。点之前的部分不是名称的一部分，它是一个“范围标识符”，一旦您“在SELECT之外”，它就会出现/被引入，该范围标识符就会“消失”。您可以使用嵌套的SELECT： SELECT A. ID From（SELECT A. ID， B. ID From...）来验证这一点。它不起作用（除非您的特定实现为了使其工作而偏离标准）。

各种SQL构造给人的印象是，表确实具有对行的排序。显然，ORDER BY子句也是GROUP BY子句（只能通过引入“行分组在一起的中间表”这一相当狡猾的概念来实现）。关系根本不是这样的。

表可以包含null，关系不能。这个人被打死了。

应该还有一些，但我记不得了。

Answer 2

不足为奇的是，Oracle将此视为一种特殊情况。你可以从执行计划中看到这一点。对于有时出现的简单（不正确/不确定）版本的限制，您可以获得排序顺序和计数停止键操作：

select *
from my_table
where rownum <= 7
order by price;

--------------------------------------------------------------------------------          
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |          
--------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT    |          |     1 |    13 |     3  (34)| 00:00:01 |          
|   1 |  SORT ORDER BY      |          |     1 |    13 |     3  (34)| 00:00:01 |          
|*  2 |   COUNT STOPKEY     |          |       |       |            |          |          
|   3 |    TABLE ACCESS FULL| MY_TABLE |     1 |    13 |     2   (0)| 00:00:01 |          
--------------------------------------------------------------------------------          

Predicate Information (identified by operation id):                                       
---------------------------------------------------                                       

   2 - filter(ROWNUM<=7)                                                                  

如果只使用有序子查询，没有限制，则只能通过操作获得排序顺序：

select *
from (
  select *
  from my_table
  order by price
);

-------------------------------------------------------------------------------           
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |           
-------------------------------------------------------------------------------           
|   0 | SELECT STATEMENT   |          |     1 |    13 |     3  (34)| 00:00:01 |           
|   1 |  SORT ORDER BY     |          |     1 |    13 |     3  (34)| 00:00:01 |           
|   2 |   TABLE ACCESS FULL| MY_TABLE |     1 |    13 |     2   (0)| 00:00:01 |           
-------------------------------------------------------------------------------           

使用通常的子查询/ROWNUM构造，您会得到不同的东西，

select *
from (
  select *
  from my_table
  order by price
)
where rownum <= 7;

------------------------------------------------------------------------------------      
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |      
------------------------------------------------------------------------------------      
|   0 | SELECT STATEMENT        |          |     1 |    13 |     3  (34)| 00:00:01 |      
|*  1 |  COUNT STOPKEY          |          |       |       |            |          |      
|   2 |   VIEW                  |          |     1 |    13 |     3  (34)| 00:00:01 |      
|*  3 |    SORT ORDER BY STOPKEY|          |     1 |    13 |     3  (34)| 00:00:01 |      
|   4 |     TABLE ACCESS FULL   | MY_TABLE |     1 |    13 |     2   (0)| 00:00:01 |      
------------------------------------------------------------------------------------      

Predicate Information (identified by operation id):                                       
---------------------------------------------------                                       

   1 - filter(ROWNUM<=7)                                                                  
   3 - filter(ROWNUM<=7)                                                                  

外部查询仍然存在计数停止键操作，但内部查询（内联视图或派生表）现在具有按停止键的排序顺序，而不是简单的按排序顺序。这一切都隐藏在内部，所以我在猜测，但它看起来像是停止键（即行数限制）被推入子查询处理中，因此实际上子查询可能只会以七行结束，尽管计划的行值没有反映这一点（但随后会得到具有不同限制的相同计划），它仍然觉得需要单独应用计数停止键操作。

Tom Kyte在Oracle杂志的一篇文章中谈到了类似的内容，当时谈到了“使用ROWNUM进行Top-N查询处理”（着重部分已添加）：

有两种方法可以实现这一点：让客户端html" target="_blank">应用程序运行该查询并仅获取前N行
-将该查询用作内联视图，并使用ROWNUM限制结果，如在SELECT*FROM（your\u query\u here）中，其中ROWNUM

由于两个原因，第二种方法远远优于第一种方法。这两个原因中较小的一个原因是它需要更少的客户端工作，因为数据库负责限制结果集。更重要的原因是数据库可以进行特殊处理，只提供前N行。使用top-N查询意味着您已经为数据库提供了额外的信息。你说过，“我只对N行感兴趣；我永远不会考虑剩下的。”现在，在你考虑排序如何工作以及服务器需要做什么之前，这听起来并不太惊天动地。

... 然后继续概述它实际上在做什么，比我能做的更权威。

有趣的是，我并不认为最终结果集的顺序是可以保证的；它似乎总是可行的，但可以说，您也应该在外部查询上有一个ORDER BY来完成它。看起来顺序并没有真正存储在子查询中，它恰好是这样生成的。（我非常怀疑这种情况是否会改变，因为它会破坏太多东西；这最终看起来类似于一个表集合表达式，它似乎也总是保留其顺序-尽管这样会停止dbms\u xplan的工作。我相信还有其他示例。）

仅供比较，这是等效的行数（）所做的：

select *
from (
  select ROW_NUMBER() OVER (ORDER BY price) rn, my_table.*
  from my_table
) t
where rn <= 7;

-------------------------------------------------------------------------------------     
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |     
-------------------------------------------------------------------------------------     
|   0 | SELECT STATEMENT         |          |     2 |    52 |     4  (25)| 00:00:01 |     
|*  1 |  VIEW                    |          |     2 |    52 |     4  (25)| 00:00:01 |     
|*  2 |   WINDOW SORT PUSHED RANK|          |     2 |    26 |     4  (25)| 00:00:01 |     
|   3 |    TABLE ACCESS FULL     | MY_TABLE |     2 |    26 |     3   (0)| 00:00:01 |     
-------------------------------------------------------------------------------------     

Predicate Information (identified by operation id):                                       
---------------------------------------------------                                       

   1 - filter("RN"<=7)                                                                    
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "PRICE")<=7)                                   

Answer 3

在这种情况下，ORDER BY实际上是Oracle的专有语法，用于在（逻辑上）无序的行集合上生成“有序”行号。在我看来，这是一个设计糟糕的功能，但等效的ISO标准SQL ROW\u NUMBER（）函数（在Oracle中也有效）可能会让它更清楚地了解正在发生的事情：

select *
from (
  select ROW_NUMBER() OVER (ORDER BY price) rn, *
  from table
) t
where rn <= 7;

在这个例子中，ORDER BY去了它更符合逻辑的地方：作为派生行号属性规范的一部分。这比Oracle的版本更强大，因为您可以指定几个不同的顺序，在同一个结果中定义不同的行号。此查询返回的行的实际顺序是未定义的。我相信在您的Oracle特定版本的查询中也是如此，因为当您以这种方式使用ORDER BY时，无法保证排序。

值得记住的是，甲骨文不是关系数据库管理系统。与其他SQLDBMS一样，甲骨文在一些基本方面偏离了关系模型。产品中存在隐式排序和DISTINCT等特性，正是因为数据SQL模型的非关系性质，以及随之而来的处理具有重复行的无键表的需要。