回顾了之前做的项目,看到查询分页功能,发现有可以优化的地方。
总的来说优化过程有三个阶段:
一、
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 做自身关联,查出所需要的字段。