使用optimize for解决分页问题

大数据量的分页问题经常会困扰我们。
这里分享一个简单实用的方法：

假设某个表 T 拥有 200万记录。
一般分页的SQL会是这样：

select *  from
( select * ,row_number() over ( order by C1 ) as rn
  from T ) a
where rn >= start_row and rn <= end_row

一般这个会比较慢，因为T表很大，全表排序效率很低，
而且每查一次都会做一次，数据库压力太大。

其实仔细想一下，一般来讲用户不需要这么多页。
如果一页20条记录，则会有10万页的结果，根本看不完。
用户一般都会只关注前面几十页。

所以我们可以加上fetch first rows only 来限制一下结果集。

SQL改写如下：

select *  from
( select * ,row_number() over ( order by C1 ) as rn
  from T fetch fist 20000 rows only) a
where rn >= start_row and rn <= end_row
（20000 只是一个举例，可以按需求调整）

但是仅仅这样还不行，全表排序免不了。
所以我们想到了索引，
Create index idx1 on T (C1)
让DB2用索引排序代替全表排序。

这样就行了吗？答案是可能还不行。
因为你是select * ，所以DB2认为走索引还不如全表排序。
尤其你的索引 cluster 不是太好的时候。

其实我们的目的是尽快出前 20000 条，其他的我们不管。
所以这时我们可以用 optimize for N rows语句，告诉DB2
你只要尽快把 20000 条出给我就行了，我不关心全表排序要多长时间。
这时 DB2 就会选择索引扫描来排序了。。。
SQL改写如下：
select *
( select * ,row_number() over ( order by C1 ) as rn
  from T  fetch first 20000 rows only) a
where rn >= start_row and rn <= end_row
optimize for 20000 rows

在真实的案例中，这句optimize for N rows将
查询从40秒调高到了3秒。