Oracle分页数据优化

oracle分页查询数据

 

一、分页计算数据

`分页的参数:

pageNumber：页码

pageSize：每页展示数据

rowNum: pageSize*pageNumber

rowNo: (pageNumber-1)*pageSize

二、没有order by分页查询数据

1.嵌套子查询利用rownum来作为行号分组数据

 SELECT *
  FROM (SELECT ROWNUM AS ROWNO, T.*
          FROM RESTORE_INFO T
         WHERE N_DATE = '20210131'
           AND ROWNUM < 5000 * 1) t1
  WHERE t1.rowno > 0;  
pageSize*pageNumber; (pageNumber - 1 ) * pageSize

2：使用between and 一次筛选（数据量大的时候，第一次查询的数据量过大，明显比上面慢，不推荐）
 

 SELECT *
   FROM (SELECT ROWNUM AS ROWNO, T.*
           FROM RESTORE_INFO T
          WHERE N_DATE = '20210131') T1
  WHERE T1.ROWNO BETWEEN 5000 * (2 - 1) + 1 AND 5000 * 2;
 endNumber: pageSize*pageNumber
 startNumber: pageSize*(pageNumber -1) + 1 

3:查询语句包含oder by 主键唯一字段（防止分页查询数据包含重复）

 SELECT * FROM (SELECT ROWNUM AS ROWNO, R.*
   FROM (SELECT *
           FROM RESTORE_INFO
          WHERE N_DATE = '20210131'
          ORDER BY VC_TRANS_ID) R
          WHERE ROWNUM < 5000*1) table_alias
          WHERE table_alias.ROWNO > (1-1)*5000;
endNumber: pageSize * pageNumber
startNumber: (pageNumber - 1) * pageSize

4：使用窗口函数row_number() over(partition by id)来进行分页

SELECT * FROM (SELECT * FROM (SELECT RI.*, ROW_NUMBER() OVER(ORDER BY VC_TRANS_ID) AS ROWNUMBER
  FROM RESTORE_INFO RI
 WHERE N_DATE = '20210131'
 ORDER BY VC_TRANS_ID) p WHERE p.ROWNUMBER < 5000 * 1)
 WHERE ROWNUMBER > (1-1)*5000;
startNumber: (pageNumber - 1) * 5000
endNumber:   pageNumber*pageSize

5: 以上数据在数据库表量很大的时候查询很慢，通过执行计划走的 TABLE ACCESS FULL

-- 先查询出来主键
SELECT ROW_.*, ROWNUM ROWNUM_
  FROM (SELECT VC_TRANS_ID
          FROM RESTORE_INFO
         WHERE N_DATE = '20210131'
         ORDER BY VC_TRANS_ID) ROW_
-- 再把主键查询出来
SELECT st.vc_trans_id FROM (SELECT ROW_.*, ROWNUM ROWNUM_
  FROM (SELECT VC_TRANS_ID
          FROM RESTORE_INFO
         WHERE N_DATE = '20210131'
         ORDER BY VC_TRANS_ID) ROW_ WHERE ROWNUM < 5000 * 1) st
--- 根据数据
 SELECT NFR.*
   FROM RESTORE_INFO NFR
  WHERE NFR.VC_TRANS_ID IN
        (SELECT S.VC_TRANS_ID
           FROM (SELECT ROW_.*, ROWNUM ROWNUM_
                   FROM (SELECT VC_TRANS_ID
                           FROM RESTORE_INFO
                          WHERE N_DATE = '20210131'
                          ORDER BY VC_TRANS_ID) ROW_
                  WHERE ROWNUM <= 5000 * 2) S
          WHERE ROWNUM_ > (2 - 1) * 5000);
startNumber : (pageNumber - 1)*pageSize
endNUmber: pageNumber * pageSize

5：使用rowid分页数据

select *
  from restore_info t1,
       (select rid
          from (select rownum rn, t.rid
                  from (select
                         rowid rid
                          from restore_info WHERE n_date = '20210131' ORDER BY vc_trans_id
                         ) t
                 where rownum <= 5000 * 1)
         where rn > (1-1)*5000) t2
 where t1.rowid = t2.rid

startNumber : pageNumber * pageSize
endNumber : (pageNumber - 1)*pageSize

---

总结

例如：以上就是oracle设计到分页数据查询；

---