使用ROW_NUMBER() OVER(ORDER BY NULL):(需要排序就写order by 字段名,但对性能影响极大)
SELECT *
FROM (SELECT A.*, ROW_NUMBER() OVER(ORDER BY NULL) RN FROM ORDER A) T
WHERE T.RN > 6039591
AND T.RN < 6039691; --115.21s
使用ROW_NUMBER() OVER(ORDER BY NULL) 配合rownum限定数量:
SELECT *
FROM (SELECT A.*, ROW_NUMBER() OVER(ORDER BY NULL) RN FROM ORDER A) T
WHERE T.RN > 8039591
AND ROWNUM < 100; --230.42s
使用ROWNUM的方式:
SELECT *
FROM (SELECT A.*, ROWNUM RN FROM ORDER A) TT
WHERE TT.RN > 7039591
AND ROWNUM < 100; --147.52 s
以上查询是在一张一亿三千多万的表中查询耗时,仅为参考值,不作为性能依据。