Oracle中SQL分页查询语句（oracle12c分页查询）

在工作中我们经常需要在Oracle中进行分页、排序、查询的组合SQL语句，举例来说，通常我们会这样写：（假定表test中id是主键，并且id从1开始没有间断顺序排列）

一、SELECT * FROM (

SELECT id,b1,b2,b3,b4,b5,b6, ROWNUM AS tempFROM 表1

WHERE b1 LIKE '%%' AND ROWNUM<=1000000

ORDER BY id

) t2 WHERE temp>=999990;

但是由于ROWNUM是一个伪列，Oracle会首先查询满足ROWNUM<=1000000条件的记录，然后再对得到的记录进行排序，这就导致我们并不能获得期望的排序结果。本应得到id为999990-1000000，实际上却得到了一些无规律并令人困惑的id结果。于是做了以下改进；

二、SELECT * FROM (

SELECT id,b1,b2,b3,b4,b5,b6, ROWNUM AS temp FROM (

SELECT id,b1,b2,b3,b4,b5,b6 FROM 表1

WHERE b1 LIKE '%%'

ORDER BY id

) t1 WHERE ROWNUM<=1000000

) t2 WHERE temp>=999990;

显然，通过经典的三层分页排序查询结构，我确实得到了想要的结果。但是在数据量很大的数据表中，由于需要首先取得排序后的全部数据集，导致了执行效率的极速降低，直至无法忍受。为了提高性能，我在网上查阅了很多资料，其中有一种说法是：当排序条件使用的关键字是主键或索引，并在WHERE子句中先于ROWNUM使用过该关键字时，我们就可以采用第一个语句获得想要的结果了，于是我改写了第一个语句并进行了测试：

三、 SELECT * FROM (

SELECT id,b1,b2,b3,b4,b5,b6, ROWNUM AS temp FROM 表1

WHERE id>=0 AND ROWNUM<=1000000

ORDER BY id

) t2 WHERE temp>=999990;

我惊喜的发现确实有效，我成功了，但惊喜并没有持续多久，因为当我把查询条件“b1 LIKE '%%'”也放回WHERE语句中时，一切又恢复了原状。于是我改写出了以下语句：

四、SELECT * FROM 表1

WHERE id IN (

SELECT id FROM (

SELECT id, ROWNUM AS temp FROM (

SELECT id FROM 表1

WHERE b1 LIKE '%%'

ORDER BY id

) t1 WHERE ROWNUM<=1000000

) t2 WHERE temp>=999990

);

这条语句通过减少最内层SELECT语句获得的数据量（仅保留必须的id），极大地提高了查询性能。但是WHERE…IN语句由于需要遍历数据表，也就是说在本语句中每查询出一个id，Oracle就需要在最后的WHERE…IN语句搜索一次并把它挑出来，所以WHERE…IN语句本身的效率并不高，本语句依然需要效率提升，那么该怎么做呢？答案就是ROWID伪列。什么是ROWID伪列呢？用最简单的话说，ROWID就是该数据行的绝对物理地址，在百度百科上我们可以查到索引就是通过ROWID来记录数据位置的。于是我们的WHERE…IN语句不再需要遍历数据表，不再需要通过一次次的检索来收集数据了，因为它得到了最终数据的直接物理地址。从这个意义上讲，WHERE…IN语句成为了最高效的语句。

改写如下：

五、SELECT * FROM 表1

WHERE ROWID IN (

SELECT rid FROM (

SELECT rid, ROWNUM AS temp FROM (

SELECT ROWID rid FROM 表1

WHERE b1 LIKE '%%'

ORDER BY id

) t1 WHERE ROWNUM<=1000000

) t2 WHERE temp>=999990

);

令人困惑的是，虽然检索出来的数据范围是正确的，但是最终的顺序是被打乱的，也就是说id虽然范围是在999990-1000000中，但相互间是没有顺序的，当然到这一步已经没有什么难度了，于是我写出了最终的语句：

SELECT * FROM 表1

WHERE ROWID IN (

SELECT rid FROM (

SELECT rid, ROWNUM AS temp FROM (

SELECT ROWID rid FROM 表1

WHERE b1 LIKE '%%'

ORDER BY id

) t1 WHERE ROWNUM<=1000000

) t2 WHERE temp>=999990

) ORDER BY id;

至此一切OK，为了对提升的效率有个直观的理解，我又对编号一、二、四、六语句进行了测试（测试数据表1共2010093条记录），各测试三次去平均值，结果如下：

一号语句（两层嵌套，而且结果并不符合预期）：25.90秒

二号语句（三层嵌套，结果符合预期，但太慢了。。。）：68.65秒

四号语句（采用WHERE id IN …，结果符合预期，效率提高）：10.09秒

六号语句（采用WHERE ROWID IN …，结果符合预期，效率最高）：5.08秒