我有一个SQL查询,看起来像这样：
SELECT * FROM(
SELECT
...,row_number() OVER(ORDER BY ID) rn
FROM
...
) WHERE rn between :start and :end
基本上,这是ORDER BY的一部分,减缓了事情.如果我删除它,EXPLAIN成本下降了一个数量级(超过1000x).我试过这个：
SELECT
...
FROM
...
WHERE
rownum between :start and :end
但这并不能给出正确的结果.有没有什么简单的方法来加速呢？或者我会用EXPLAIN工具花更多的时间吗？
解决方法
在Oracle中,ROW_NUMBER是非常低效的.
> Oracle: ROW_NUMBER vs ROWNUM
对于您的具体查询,我建议您将其替换为ROWNUM,并确保使用索引：
SELECT *
FROM (
SELECT /*+ INDEX_ASC(t index_on_column) NOPARALLEL_INDEX(t index_on_column) */
t.*,ROWNUM AS rn
FROM table t
ORDER BY
column
)
WHERE rn >= :start
AND rownum <= :end - :start + 1
此查询将使用COUNT STOPKEY
还要确保您的列不可为空,或者添加WHERE列IS NOT NULL条件.
否则索引不能用于检索所有值.
请注意,您不能在没有子查询的情况下使用ROWNUM BETWEEN：start和：end.
ROWNUM总是最后分配,最后检查,这是ROWNUM始终按顺序排列.
如果使用10和20之间的ROWNUM,满足所有其他条件的第一行将成为返回的候选项,临时分配为ROWNUM = 1,并且不能在10和20之间的ROWNUM测试.
那么下一行将是一个候选人,分配给ROWNUM = 1并且失败等等,所以最后没有任何行将被返回.
