Oracle分页查询

时间:2023-09-06 13:40:44

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;