oracle 分页查询优化

在sql优化过程当中，发现分页查询的几种写法性能差别较大

第一种

SELECT * FROM (
SELECT row_number() OVER(ORDER BY null) as rn, a.*
FROM TABLE_NAME a
WHERE TO_DATE(substr(a.DE, 0, 8), 'YYYY-MM-DD')>=TO_DATE(:1 , 'YYYY-MM-DD')
) A
WHERE A.RN > :24867
AND A.RN <= :24868 ；
第二种
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= :24867
)
WHERE RN >=:24868

第二种性能更好，建议使用第二种做分页查询

 oracle 12c 以后分页查询可以使用top-n语句 ，详见官方文档 SQL for Analysis and Reporting (oracle.com)

 

--取前10行

select A.object_id,A.object_name from yz.t1 A  fetch first 10 rows only;

--取第二个10行

select A.object_id,A.object_name from yz.t1 A  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

--取第3个10行，后面依次类推

select A.object_id,A.object_name from yz.t1 A  OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

也可以按百分比分页

select A.object_id,A.object_name from yz.t1 A  FETCH FIRST 0.0005 PERCENT ROWS ONLY;

select A.object_id,A.object_name from yz.t1 A  OFFSET 25 ROWS FETCH FIRST 0.0005 PERCENT ROWS ONLY;

从第26行开始取值

 

WITH TIES

   与最后一行最后一列相同的其他行

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS ONLY;

EMPLOYEE_ID LAST_NAME SALARY
----------- --------------------- ------
132 Olson 2100
128 Markle 2200
136 Philtanker 2200
127 Landry 2400
135 Gee 2400
119 Colmenares 2500

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS WITH TIES;

EMPLOYEE_ID LAST_NAME SALARY
----------- --------------------- ------
132 Olson 2100
128 Markle 2200
136 Philtanker 2200
127 Landry 2400
135 Gee 2400
119 Colmenares 2500
131 Marlow 2500
140 Patel 2500
144 Vargas 2500
182 Sullivan 2500
191 Perkins 2500