Oracle的order by排序稳定么？

前几天跟个好兄弟探讨了一个问题，他用的某国产数据库通过order by排序的时候，重复记录每次查询显示的顺序是不同的，看来这个国产数据库order by的实现逻辑不是很稳定的。

不同数据库的order by实现逻辑可能不同，Oracle的逻辑是什么？猜测根据插入顺序进行排序的？

创建测试表，插入数据，

SQL> create table test (id number, c1 varchar2(1), c2 varchar2(2));
Table created.

SQL> select a.*, rownum from test a;
ID C1 C2 ROWNUM

     1 a  aa          1
     3 b  bb          2
     2 c  cc          3
     4 d  dd          4
     3 e  ee          5
     2 g  gg          6

6 rows selected.
朋友们可能看到，语句中带着rownum这个字段，和rowid一样，都是伪列，不同的是rowid是个固定值，表示这条记录的物理位置，而rownum不是固定值，它就是Oracle顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。再通俗地讲，rownum就是Oracle根据返回数据的顺序给它的一个编号，谁先返回谁就是1，如果不存在order by排序条件，那么它就是Oracle的存储顺序。

如果上述表指定order by，按照id排序，可以看到，id相同的记录，如果其它字段有重复的，显示不是固定的。例如id=2的记录，有两条，一个是c1=c，一个是c1=g，从返回上看，先显示c1=c的（先插入），再显示c1=g的（后插入）；id=3的记录，有两条，一个是c1=b，一个是c1=e，但是返回上，先显示c1=e的（后插入），再显示c1=b的（先插入），

SQL> select a.*, rownum from test a order by id;
ID C1 C2 ROWNUM

     1 a  aa          1
     2 c  cc          3
     2 g  gg          6
     3 e  ee          5
     3 b  bb          2
     4 d  dd          4

6 rows selected.
因此，从以上实验，我们可以看到，如果存在排序字段id重复记录的，返回数据的顺序则是不固定的，结合上述讲的rownum，可以推断出，顺序不固定，返回的rownum，则可能是不同的，尤其当数据较多的时候，这个现象更明显。因此，如果用这种逻辑进行分页等，就可能出现错误，究其原因就是order by排序字段不唯一。

如果针对这种SQL，需要确定的排序，有两种解决方案，

SELECT * FROM
(SELECT t.*, ROWNUM AS rowno
FROM ( select * from table ORDER BY LIST_ORDER) t
WHERE ROWNUM < #endRow# ) WHERE rowno >= #startRow#
方案一，改写SQL

SELECT * FROM
(SELECT t.*, ROWNUM AS rowno FROM
(select * from table ORDER BY LIST_ORDER) t)
WHERE rowno >= #startRow# AND ROWNUM < #endRow#
因为内部取值SQL是不变的，所以取值的顺序肯定是固定的。但是要注意，由于内部采用了全表扫描，因此方案选择上，需要结合实际的数据量和场景需求进行决策。

方案二，order by增加唯一性字段

order by加主键、唯一索引、唯一约束字段、rowid等。但是要注意，同样有性能问题，毕竟有多个字段的排序，需要结合场景进行相应的优化。

和rownum相关的历史文章如下，
《从rownum的SQL需求还能归纳出的知识》
《rownum的SQL需求》
《从一条”错误”的SQL，了解rownum的作用》

参考资料，
https://www.cnblogs.com/lwlxqlccc/p/8676045.html