Oracle分页查询

FIRST OF ALL:

　　论效率,还是ROWID的高(Oracle11g+版本).经过几轮测试,发现使用ROWID进行分页,特别是页数很大的情况下,效率能提高十倍多.so,Mark下代码:

SELECT name, age, gender, grade, class, score

　　FROM students

WHERE ROWID IN (
　　SELECT rid
　　　　FROM (

　　　　　　SELECT ROWNUM rn, rid 
　　　　　　　　FROM (

　　　　　　　　　　SELECT ROWID rid 
　　　　　　　　　　　　FROM students WHERE age > 20 ORDER BY score DESC

    　　　　　　) WHERE ROWNUM <= 300010
　　　　) WHERE rn >= 300001
) ORDER BY score DESC;

　　由于上述写法比较麻烦,而且一般情况下也没人会一页一页的翻到几十万页来看个东西.so,日常使用的还是ROWNUM,对于靠前的页数查询,其效率和ROWID差不很多. 代码:

SELECT * FROM (
　　SELECT t.*, ROWNUM as rn FROM (
　　　　SELECT * FROM T_DUAL WHERE ... ORDER BY XXX

　　) t

) WHERE rn BETWEEN 11 AND 20;

1.无ORDER BY排序的写法。(效率最高)

经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！

sql语句如下：

SELECT *

 FROM (Select ROWNUM AS ROWNO, T.*

      from k_task T

     where Flight_date between to_date('', 'yyyymmdd') and

        to_date('', 'yyyymmdd')

      AND ROWNUM <= 20) TABLE_ALIAS

WHERE TABLE_ALIAS.ROWNO >= 10;

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

经过测试，此方法随着查询范围的扩大，速度也会越来越慢！

sql语句如下：

SELECT *

 FROM (SELECT TT.*, ROWNUM AS ROWNO

      FROM (Select t.*

          from k_task T

          where flight_date between to_date('', 'yyyymmdd') and

             to_date('', 'yyyymmdd')

          ORDER BY FACT_UP_TIME, flight_no) TT

     WHERE ROWNUM <= 20) TABLE_ALIAS

where TABLE_ALIAS.rowno >= 10;

3.无ORDER BY排序的写法。(建议使用方法1代替)

此方法随着查询数据量的扩张，速度会越来越慢！

sql语句如下：

SELECT *

 FROM (Select ROWNUM AS ROWNO, T.*

      from k_task T

     where Flight_date between to_date('', 'yyyymmdd') and

        to_date('', 'yyyymmdd')) TABLE_ALIAS

WHERE TABLE_ALIAS.ROWNO <= 20

  AND TABLE_ALIAS.ROWNO >= 10;

TABLE_ALIAS.ROWNO between 10 and 100;

4.有ORDER BY排序的写法.(建议使用方法2代替)

此方法随着查询范围的扩大，速度也会越来越慢！

sql语句如下：

SELECT *

 FROM (SELECT TT.*, ROWNUM AS ROWNO

      FROM (Select *

          from k_task T

          where flight_date between to_date('', 'yyyymmdd') and

             to_date('', 'yyyymmdd')

          ORDER BY FACT_UP_TIME, flight_no) TT) TABLE_ALIAS

where TABLE_ALIAS.rowno BETWEEN 10 AND 20;

5.另类语法

该语法风格与传统的SQL语法不同，不方便阅读与理解，为规范与统一标准，不推荐使用。此处贴出代码供大家参考之用。

sql语句如下：

With partdata as(

 SELECT ROWNUM AS ROWNO, TT.* FROM (Select *

         from k_task T

         where flight_date between to_date('', 'yyyymmdd') and

            to_date('', 'yyyymmdd')

         ORDER BY FACT_UP_TIME, flight_no) TT

  WHERE ROWNUM <= 20)

  Select * from partdata where rowno >= 10;

6. 拼装sql和分页.(有order by)

SELECT r.*

FROM (SELECT

        t.*, ROWNUM AS rn

      FROM ( SELECT

               xm AS name, nl AS age, xb AS gender

             FROM test WHERE mx LIKE '%william%' ORDER BY name ) t) r

WHERE r.rn BETWEEN 0 AND 10;

　　整理下,就是这样的:(懒得注释,自己猜)

SELECT r.* FROM (SELECT t.*, ROWNUM AS rn FROM (" + innerSql + orderBy + ") t ) r WHERE r.rn <= " + end + " AND r.rn > " + start

7.另类语法。(无ORDER BY写法）

With partdata as(

 Select ROWNUM AS ROWNO, T.*

  From K_task T

  where Flight_date between to_date('', 'yyyymmdd') and

     To_date('', 'yyyymmdd')

   AND ROWNUM <= 20)

  Select * from partdata where Rowno >= 10;

巴特西

Oracle分页查询

最新文章

热门文章