Oracle数据库分页查询语句与优化

回顾了之前做的项目，看到查询分页功能，发现有可以优化的地方。
总的来说优化过程有三个阶段：
一、

select * from (select A.*, rownum rn from (select * from table_name)A）  where rn between 21 and 40;

这条分页查询语句最为简单，也很好理解，老套路三层嵌套循环，最内层先查出所有记录，次层查出所有记录以及记录伪列，最外层根据伪列查询所有下限到上限的记录。这条查询语句虽然简单，但是查询效率很低，每一次查询都要把所有记录查一次，查询效率对于数据量很大的情况来说很难接受。

---

二、

select * from (select A.*, rownum rn from (select * from table_name)A where rowmun <= 40） where rn > 21;

这个查询也很容易理解，第一层同样先查询所有记录，第二次根据伪列rownum来过滤掉上限以外的记录，最外层根据伪列下限截取记录。大多数情况下，这个查询要比第一个查询语句快得多，因为Oracle在基于代价优化的机制下，会把第二次的查询条件提到内层，这样的话，每次查询只是查询一到伪列上限条记录，比第一条查询语句的每次查询所有记录要好得多。那么为什么说是“大多数情况下”呢，因为随着页数往后翻，伪列得上限也就越接近于所有记录，也就是说，即使在优化的情况下，第二条查询语句的查询记录数会越来越接近第一条查询语句，等到翻到最后一页的时候，两条查询语句效率就相同了。

---

三、

select * from table_name t (select rd from (select rowid rd, rownum rn from table_name where rownum <= 40)  where rn > 20)t1 where t.rowid = t1.rd;

这条语句是在第二条语句的情况下做了优化，基本原理大同小异，用伪列rowid 来做表自身的关联，内层查询只查询了rownum<=40的两个字段rowid 和rownum，避免了使用 * ，次层查询查 rownum>20的 rowid字段，这层也避免了 * ，最外层用查询出来的rowid 和原表的rowid 做自身关联，查出所需要的字段。