oracle分页

转载

120万条数据测试

1.无ORDER BY排序的写法。(效率最高)--（0.079秒）

SELECT *
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM table t
         WHERE 1=1
         AND hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                             AND TO_DATE ('20060731', 'yyyymmdd')

           AND ROWNUM <= 20) table_alias
 WHERE table_alias.rowno >= 10;

2.有ORDER BY排序的写法。(效率较高) (20秒)

 

 SELECT *
  FROM (SELECT tt.*, ROWNUM AS rowno
          FROM (  SELECT t.*
                    FROM emp t
                   WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                                       AND TO_DATE ('20060731', 'yyyymmdd')
                ORDER BY create_time DESC) tt
         ) table_alias
 WHERE table_alias.rowno >= 10
 AND table_alias.rowno <= 20;