我有一个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并且失败等等,所以最后没有任何行将被返回.