V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
xcc7624
V2EX  ›  数据库

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

  •  
  •   xcc7624 · 2017-12-23 11:13:49 +08:00 · 1078 次点击
    这是一个创建于 2287 天前的主题,其中的信息可能已经有所发展或是发生改变。

    各位大神求帮忙啊 为什么用第二种写法分十次查出 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;
    
    第 1 条附言  ·  2017-12-23 15:22:25 +08:00

    补充一下 在之前的语句上,最外层的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 条回复    2017-12-24 09:58:00 +08:00
    dong3580
        1
    dong3580  
       2017-12-23 11:20:41 +08:00 via Android   ❤️ 1
    第一个先排序所有的,再从排序后取出 0 - 100 之间的,
    第二个排序 0 - 100 并取出。
    两个查询的范围压根不一样吧?

    有几年没写 PL/SQL 了,弱弱的问一句,表一共只有 100 条数据?
    xcc7624
        2
    xcc7624  
    OP
       2017-12-23 11:24:48 +08:00
    @dong3580 这个一共套了三层子查询,两个 SQL 最里面的都是做的全表排序的啊,不止 100 条
    bxb100
        3
    bxb100  
       2017-12-23 11:42:52 +08:00 via Android   ❤️ 1
    rownum 产生实在表记录扫描时产生,排序后进行,你可以抽出来自己看一下,rownum 是乱的. 你可以先排序,然后对结果分页
    select * from ( select rownum r, t.* from (select * from user_info order by name) t where rownum <= 100) where r > 0
    xcc7624
        4
    xcc7624  
    OP
       2017-12-23 11:47:20 +08:00 via Android
    @bxb100 我查过 oracle 的官网 rownum 确实你说的那样,order by 的字段有没有索引对 rownum 也有影响,你的写法是我上面的第二种吧?种写法得出的结果分页查出来数据有重复的
    bxb100
        5
    bxb100  
       2017-12-23 12:13:56 +08:00 via Android   ❤️ 1
    @xcc7624 恩,我理解错了,忽略我 😂
    sun1991
        6
    sun1991  
       2017-12-23 18:22:45 +08:00 via Android
    第二个查询,select ... from tb, 即使 tb 本身进行了排序,也不能保证 select 结果是有序的。顺序必须用 order by 来保证,但是 order by 是在数据返回后才进行计算的。
    xcc7624
        7
    xcc7624  
    OP
       2017-12-24 09:58:00 +08:00
    @sun1991 在第二个里面加 order by 也不对啊
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5053 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 01:15 · PVG 09:15 · LAX 18:15 · JFK 21:15
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.