一个分批取数优化例子

一个oracle分批取数优化例子：
ALLOBJ表是一个100w的大表，需要对它进行分批取数：

通用分页sql：
Select * From (Select a.*, Rownum Rn From (Select * From ALLOBJ) a Where Rownum <= 10000) Where Rn > 5000
优点：1通用 2查询前面几页的时候性能还不错

 

缺点：表非常大时，查询后几页性能比较差，如：
Select * From (Select a.*, Rownum Rn From (Select * From ALLOBJ) a Where Rownum <= 500000) Where Rn > 495000
oracle会从硬盘读取前50w条记录再去掉前面的495000条，也就是说越到后面越接近于全表扫描了

 

所以必须对这个sql进行优化，当我们要获取整个表的较少数据时，当然用索引比较快，应该尽量用到索引，还好ALLOBJ表有一个唯一索引object_id,
那先对索引分批，再按索引取表里的数据就会快多了，因此改写如下：
先取出每一页的最大id
Select object_id maxid, rn / 5000 page
From (Select object_id, Rownum Rn From (Select object_id From ALLOBJ Where object_id Is Not Null Order By object_id)) a
Where Mod(rn, 5000) = 0

 

然后
Select * From allObj Where object_id > 1657966 And object_id <= 1662966

注意： 如果object_id是nullable，那么Select object_id From ALLOBJ 仍然会table Access Full, 因为索引里不保存null，所以要加个条件object_id Is Not Null，这样对索引分页时只需要读取索引，就很快了