Oracle 分页

分页查询

在 Oracle 中使用分页

1. 无 order by 查询

1.1 嵌套子查询，两次筛选，推荐

SELECT *
FROM (
  SELECT ROWNUM AS rowno, t.*
  FROM DONORINFO t
  WHERE t.BIRTHDAY BETWEEN TO_DATE('20180101', 'yyyymmdd') AND TO_DATE('20190101', 'yyyymmdd')
    AND ROWNUM <= 20 * 2
) table_alias
WHERE table_alias.rowno > 20 * (2 - 1);        
--耗时0.05s

1.2. 一次筛选（数据量大的时候，第一次查询的数据量过大，明显比上面慢，不推荐）

SELECT *
FROM (
  SELECT ROWNUM AS rowno, t.*
  FROM DONORINFO t
  WHERE t.BIRTHDAY BETWEEN TO_DATE('20180101', 'yyyymmdd') AND TO_DATE('20190101', 'yyyymmdd')
) r
WHERE r.rowno BETWEEN 20 * (2 - 1) + 1 AND 20 * 2;      
--耗时0.46s

2. 有 order by 查询

2.1 嵌套子查询，两次筛选，推荐

SELECT *
FROM (
  SELECT ROWNUM AS rowno, r.*
  FROM (
      SELECT *
      FROM DONORINFO t
      WHERE t.BIRTHDAY BETWEEN TO_DATE('20180101', 'yyyymmdd') AND TO_DATE('20190101', 'yyyymmdd')
      ORDER BY t.BIRTHDAY DESC
  ) r
  WHERE ROWNUM <= 20 * 2
) table_alias
WHERE table_alias.rowno > 20 * (2 - 1);
-- 耗时0.744

2.2. 一次筛选（数据量大的时候，第一次查询的数据量过大，明显比上面慢，不推荐）

SELECT *
FROM (
  SELECT ROWNUM AS rowno, r.*
  FROM (
    SELECT *
    FROM DONORINFO t
    WHERE t.BIRTHDAY BETWEEN TO_DATE('20180101', 'yyyymmdd') AND TO_DATE('20190101', 'yyyymmdd')
    ORDER BY t.BIRTHDAY DESC
  ) r
-- where ROWNUM <= 20; --这里用>查不到数据 =也查不到数据 <= 或者 < 可以查到数据 
-- where ROWNUM BETWEEN 20*(2-1)+1 AND 20*2; --查不到数据
-- where ROWNUM <=20*2 and ROWNUM > 20*(2-1); --查不到数据
-- 这是因为查询时，第一条生成的rownum为1,1>20不成立，1=20也不成立，所以这条数据就作废了，依次类推，这样就查不到任何一条数据
) t
WHERE t.rowno <= 20 * 2
  AND t.rowno > 20 * (2 - 1);-- 可以查到数据耗时：3.924s
-- where t.rowno BETWEEN 20*(2-1)+1 AND 20*2; --可以查到数据耗时：3.919

3. 采用 row_number() over 分页函数

SELECT *
FROM (
  SELECT d.*, row_number() OVER (ORDER BY d.BIRTHDAY) AS rownumber
  FROM DONORINFO d
  WHERE d.BIRTHDAY BETWEEN TO_DATE('20180101', 'yyyymmdd') AND TO_DATE('20190101', 'yyyymmdd')
) p
WHERE p.rownumber BETWEEN 20 * (2 - 1) + 1 AND 20 * 2;
-- 耗时0.812