ORACLE几种分页查询SQL语句

一、效率高的写法

1.无ORDER BY排序的写法。(效率最高)
(经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!)

SELECT *

  FROM (SELECT ROWNUM AS rowno, t.*

          FROM emp t

         WHERE 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排序的写法。(效率较高)
(经过测试,此方法随着查询范围的扩大,速度也会越来越慢)

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, emp_no) tt

         WHERE ROWNUM <= 20) table_alias

 WHERE table_alias.rowno >= 10;

二、效率垃圾但又似乎很常用的分页写法

SELECT *

  FROM (SELECT ROWNUM AS rowno, t.*

          FROM k_task t

         WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                               AND TO_DATE ('20060731', 'yyyymmdd')) table_alias

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

--TABLE_ALIAS.ROWNO  between 10 and 100;

 

上一篇:Oracle 伪列(ROWNUM)机制


下一篇:oracle的分页查询