Oralce一个rownum的诡异问题。。

inoracle · 发表于 2012-5-14 17:53

本帖最后由 inoracle 于 2012-5-14 17:54 编辑

在oracle进行查询前几条数据的时候，使用到了rownum，之前一直没有注意到一个小问题，今天无意发现了。
特拿出来请教各位，望各位高人能讨论给出关注和释惑：

句子很简单：
根据薪资sal降序排序，查出所有数据，这里使用了子查询对结果进行操作，外部查询输出了子查询结果的rownum，这个值在目前数据确定的情况下应该是固定的：
select empno,ename,sal,rownum from
(select empno,ename,sal from emp where sal is not null order by sal desc)

	EMPNO	ENAME	SAL	ROWNUM
1	7839	KING	5000.00	1
2	7902	FORD	3000.00	2
3	7788	SCOTT	3000.00	3
4	7566	JONES	2975.00	4
5	7698	BLAKE	2850.00	5
6	7782	CLARK	2450.00	6
7	7499	ALLEN	1600.00	7
8	7844	TURNER	1500.00	8
9	7934	MILLER	1300.00	9
10	7521	WARD	1250.00	10
11	7654	MARTIN	1250.00	11
12	7369	SMITH	1120.00	12
13	7876	ADAMS	1100.00	13
14	7900	JAMES	950.00	14

(注意前三条数据的顺序,这里是对子查询的数据再查询，所以rownum应该已经确定了)

现在我们给外部这个查询结果加上条件，取rownum<=3的，即刚的rownum为1,2,3的前三条数据(KING,FORD,SCOTT):
select empno,ename,sal,rownum from
(select empno,ename,sal from emp where sal is not null order by sal desc)
where rownum<=3 order by rownum asc

EMPNO	ENAME	SAL	ROWNUM
7839	KING	5000.00	1
7788	SCOTT	3000.00	2
7902	FORD	3000.00	3

(注意：本来rownum为1,2,3的前三条数据为(KING,FORD,SCOTT)，但是通过rownum<=3取出来之后，1,2,3对应的前三条数据为（KING,SOCTT,FORD）
)

很多人瞄一眼就会说rownum是不确定的，rowid才是确定的，这是非常潦草而没有认真思考的回答。
至少你应该回答当使用rownum做取前几条值的时候可能取完后会有自己新的排序方式。但是如果真的存在这样的问题（事实来看确实如此），就可能给我们取值带来不可预料的问题。不是吗，本来我们观测认为（或者希望）的前三条数据的顺序不该是(KING,FORD,SCOTT)吗？？？
所以，这绝对不是一个简单的rownum问题。

还望不吝赐教,以释疑惑。

lastwinner · 发表于 2012-5-14 18:20

你的order by并没有明确确定2/3两条数据的顺序，所以你前后两个不同的sql是会出现差别
rownum作为限制条件有时候会改变执行计划的（STOP KEY），你将语句在你的数据库里的执行计划都贴出来看看

dingjun123 · 发表于 2012-5-14 19:20

排序的那两个sal都是3000，相同值的排序，结果是不确定的
你要钱不一样，不可能出现这问题

〇〇 · 发表于 2012-5-14 19:32

SQL> delete emp;

已删除7行。

已用时间:  00: 00: 00.00
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL> insert into emp(ename,sal) values(1,5000);

已创建 1 行。

已用时间:  00: 00: 00.00
SQL> insert into emp(ename,sal) values(2,3000);

已创建 1 行。

已用时间:  00: 00: 00.00
SQL> insert into emp(ename,sal) values(3,3000);

已创建 1 行。

已用时间:  00: 00: 00.00
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL> select  empno,ename,sal,rownum from
  2  (select empno,ename,sal from emp where sal is not null order by sal desc)
  3  /

   EMPNO ENAME          SAL    ROWNUM
---------- ---------- ---------- ----------
         1             5000       1
         2             3000       2
         3             3000       3

已用时间:  00: 00: 00.00
SQL> select  empno,ename,sal,rownum from
  2  (select empno,ename,sal from emp where sal is not null order by sal desc)
  3  where rownum<=3 order by rownum asc
  4  /

   EMPNO ENAME          SAL    ROWNUM
---------- ---------- ---------- ----------
         1             5000       1
         2             3000       2
         3             3000       3

已用时间:  00: 00: 00.00
SQL>