大数据量翻页查询的一点经验

最近常有人询问翻页查询优化的问题，刚好这些天做的东西和大数据量翻页查询相关，
现在把一些经验写下来，供大家参考，同时也请大侠们指点！

大数据量翻页查询的一点经验

最近常有人询问翻页查询优化的问题，刚好这些天做的东西和大数据量翻页查询相关，
现在把一些经验写下来，供大家参考，同时也请大侠们指点！

我的系统为：每天新增一张表，每张表的数据量为1000万条记录。一共有十个字段，
有四个字段需要排序，每个字段都可能出现在查询条件中，每次查询所有的字段必须都
查询出来，可以查询三天之内的数据，没有和其他表的连接。此例只举单表查询。多表
查询稍加改造即可。

1.一般输入查询条件后，点击查询，此时查询的结果一般不用排序，此时如果查询条件
比较松，则查询的总记录条数比较多，但通常用户没有耐心翻很多页，一般用户翻上十
页就很多了，每页1000条，我测试过在99001条－－100000条的翻页仍然比常用的翻页
查询（即SQL语句3）快的多，用户此时如果查询条件比较紧，则查询的总记录条数比较少，
更不会是问题，所以应该使用此种查询语句：

       
        SQL语句1（查询第一页）
select 1,
       col01,
       col02,
       col03,
       col04,
       col05,
       col06,
       col07,
       col08,
       col09,
       col10
  from tbl_log_20040101
 where col01 > 1111 and col03 < 44444 and rownum between 1 and 1000

        SQL语句2（查询第N页）
select rownum num,
       col01,
       col02,
       col03,
       col04,
       col05,
       col06,
       col07,
       col08,
       col09,
       col10
  from tbl_log_20040101
 where col01 > 1111 and col03 < 44444 and rownum between 1 and 2000
 minus
 select rownum num,
       col01,
       col02,
       col03,
       col04,
       col05,
       col06,
       col07,
       col08,
       col09,
       col10
  from tbl_log_20040101
 where col01 > 1111 and col03 < 44444 and rownum between 1 and 1000

说明：
a.SQL语句2，为什么增加一列num？
因为minus会导致查询记录先排序，然后才减，这样导致查询出来的记录是排序的，并
且最主要的是会将完全相同的记录剔除。
b.SQL语句1，为什么增加一列1？
是为了和SQL语句2的列数相同。

2.一般输入查询条件后，点击查询，此时查询的结果一般不用排序，用户再点击某个需
要排序的字段进行升序或者降序排序，注意，此时是所有符合条件的记录的排序，不是
页面记录的排序，由于要排序，不能再使用以上两种SQL语句，我只能在需要排序的四
个字段上创建索引，如果需要以col01字段排序，就创建一个包含col01,col02,col03,
col04,col05,col06,col07,col08,col09,col10所有字段和顺序的符合索引，如果还
需要以col02字段排序，就创建一个包含col02,col01,col03,col04,col05,col06,
col07,col08,col09,col10所有字段和顺序的符合索引。这样空间的需求会大大增加，
不知是否有更好的创建和使用索引的方法，此时查询应该使用以下SQL语句：

            SQL语句3
select /*+ first_rows */
       1,
       col01,
       col02,
       col03,
       col04,
       col05,
       col06,
       col07,
       col08,
       col09,
       col10
  from (select col01,
               col02,
               col03,
               col04,
               col05,
               col06,
               col07,
               col08,
               col09,
               col10,
               rownum num
          from (select col01,
                       col02,
                       col03,
                       col04,
                       col05,
                       col06,
                       col07,
                       col08,
                       col09,
                       col10
                  from tbl_log_20040101
                 where col01 > 1111 and col03 < 44444
                 order by 2 desc))
 where num between 1 and 1000
 

说明：此查询为降序，升序比较简单，不用解释
a.SQL语句3，为什么使用提示/*+ first_rows */ ？
如果没有增加提示，在按照降序排序时查询用到了sort，如果数据量很大时我发现总会用
到磁盘排序，查询速度很慢，explain plan如下：
LPAD("",4*LEVEL)||OPERATION||"
------------------------------------------------------------------
    VIEW()
        COUNT()
            VIEW()
                SORT(ORDER BY)
                    INDEX(RANGE SCAN) IDX_LOG_20040101_COL03 NON-UNIQUE

增加该提示后，在按照降序排序时使用了索引降序，不用sort，所以查询速度很快，
explain plan如下：
LPAD("",4*LEVEL)||OPERATION||"
-------------------------------------------------------------------
    VIEW()
        COUNT()
            VIEW()
                INDEX(FULL SCAN DESCENDING) IDX_LOG_20040101_COL02 NON-UNIQUE
b.SQL语句3，为什么增加一列1？
是为了和SQL语句2的列数相同。