使用 oracle 的 rownum 伪列做分页查询遇到疑惑

This topic created in 3062 days ago, the information mentioned may be changed or developed.

各位大神求帮忙啊为什么用第二种写法分十次查出 100 条数据得出来的结果有重复的，而且就算是直接用下面的写法一次得到 100 条数据，得到结果也不一样，name 字段是没有索引的。比如

exa_00 得到结果

id name
1 wang
2 liu

exa_01 得到的结果却是

id name
1 liu
2 wang

--exa_00
select * from
    (select rownum id, tb.* 
        from
        (select name from user_info order by name) tb
    )
where id> 0 and id<100;
--exa_01
select * from
    (select rownum id, tb.* from
        (select name from user_info order by name) tb
    where rownum <100)
where id >0;

Supplement 1 · Dec 23, 2017

补充一下在之前的语句上，最外层的select中加了个子查询，explain plan 出来，差别在非常大，exa_02的耗时是exa_03的10倍

--exa_02
select out_tb.*
            (select contract_name c from contract where c.contract_name=out_tb. name) as contract
from
    (select rownum id, tb.* 
        from
        (select name from user_info order by name) tb
    ) out_tb
where id> 0 and id<100;

--exa_03
select out_tb.*,
 (select contract_name c from contract where c.contract_name=out_tb. name) as contract
from
    (select rownum id, tb.* from
        (select name from user_info order by name) tb
    where rownum <100) out_tb
where id >0;

7 replies • 2017-12-24 09:58:00 +08:00

dong3580

Dec 23, 2017 via Android

第一个先排序所有的，再从排序后取出 0 － 100 之间的，
第二个排序 0 － 100 并取出。
两个查询的范围压根不一样吧？

有几年没写 PL/SQL 了，弱弱的问一句，表一共只有 100 条数据？

xcc7624

Dec 23, 2017

@dong3580 这个一共套了三层子查询，两个 SQL 最里面的都是做的全表排序的啊，不止 100 条

bxb100

Dec 23, 2017 via Android

rownum 产生实在表记录扫描时产生，排序后进行，你可以抽出来自己看一下，rownum 是乱的. 你可以先排序，然后对结果分页
select * from ( select rownum r, t.* from (select * from user_info order by name) t where rownum <= 100) where r > 0

xcc7624

Dec 23, 2017 via Android

@bxb100 我查过 oracle 的官网 rownum 确实你说的那样，order by 的字段有没有索引对 rownum 也有影响，你的写法是我上面的第二种吧？种写法得出的结果分页查出来数据有重复的

bxb100

Dec 23, 2017 via Android

@xcc7624 恩，我理解错了，忽略我 😂

sun1991

Dec 23, 2017 via Android

第二个查询，select ... from tb, 即使 tb 本身进行了排序，也不能保证 select 结果是有序的。顺序必须用 order by 来保证，但是 order by 是在数据返回后才进行计算的。

xcc7624

Dec 24, 2017

@sun1991 在第二个里面加 order by 也不对啊