分页查询的sql优化

　　最近想向sql语句方向努力下，发现自己在做的项目中分页的sql效率太低，思路是先根据查询条件包括排序得到DataTable，然后再根据页码传递给页面相应条数的记录。后来再网上查了相关资

料，发现用索引的方法能更有效率的进行分页，现在记录下。

具体用到了rownum

　　 rownum是oracle生成结果集后加上的一个伪列。刚才是我想到的sql语句该这样写：

select * from AMS_TERMSASSET  where rownum between 0 and 10; 

　　这个和where rownum>0 and rownum<=10类似，结果发现后来变成rownum>10 and rownum<=20就返回结果为空 。

后来改进了一下sql

select * from(select t.*,rownum num from ams_termsasset t)b where b.num between 0 and 10;

　　发现这样能够正常的进行分页，但是如果在子查询中加上order by的时候就会出现序号混乱，这是因为先生成了rownum然后再进行了orderby，所以序号都被打乱了，那么如何先orderby再生成rownum呢，通过查资料和自己思考做了一层嵌套：

select * from (select b.*,rownum num from(select t.* from ams_termsasset t order by ter_id desc)b) where num between 0 and 10;

　　这次是对了，但是貌似效率不是最好的，查资料发现标准的分页sql语句该这样写：

select * from (select b.*,rownum num from(select t.* from ams_termsasset t order by ter_id desc)b where rownum<=10) where num>0;

后一种比上一种查询效率一般情况下会高出很多，由于自己对数据库研究得不深，貌似是oracle的CBO的优化策略导致可以把rownum<=10拿到最里面的子sql里面可以让子sql根据这一限定条件返回更少的结果而如果在最外面写是把所有的结果都查出来后最后再根据rownum来取其中的10条数据，两种效率差距显而易见，下面是参考资料，非常详细，可供大家学习：

http://yangtingkun.itpub.net/post/468/100278（看分页查询系列）

http://www.cnblogs.com/advocate/archive/2010/08/20/1804816.html