有没有办法使一个Oracle查询行为像它包含一个MySQL限制子句?
在MySQL中,我可以这样做:
select *
from sometable
order by name
limit 20,10
要得到第21行到第30行(跳过前20行,给出接下来的10行)。这些行是按顺序选择的,所以实际上是从第20个名字的字母顺序开始的。
在Oracle中,人们唯一提到的是rownum伪列,但它在order by之前求值,这意味着:
select *
from sometable
where rownum <= 10
order by name
将返回一个随机的10行按名称排序的集合,这通常不是我想要的。它也不允许指定偏移量。
您可以为此使用子查询
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
还可以查看Oracle/AskTom上的关于ROWNUM和限制结果的主题以获得更多信息。
更新:
为了同时限制结果的下界和上界,事情变得有点臃肿
select * from
( select a.*, ROWNUM rnum from
( <your_query_goes_here, with order by> ) a
where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
(复制自指定的AskTom-article)
更新2:
从Oracle 12c(12.1)开始,就有了限制行或从偏移量开始的语法。
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
更多的例子请看这个答案。谢谢克鲁米亚的提示。
我为以下方法做了一些性能测试:
Asktom
select * from (
select a.*, ROWNUM rnum from (
<select statement with order by clause>
) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW
分析
select * from (
<select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW
短的替代
select * from (
select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW
结果
表有1000万条记录,排序在一个未索引的datetime行上:
解释计划对所有三个选择显示相同的值(323168)
但是赢家是AskTom (analytic紧随其后)
选择前10行需要:
AskTom: 28-30秒
分析型:33-37秒
短选择:110-140秒
在100,000和100,010之间选择行:
AskTom: 60秒
分析性:100秒
在9,000,000和9,000,010之间选择行:
AskTom: 130秒
分析型:150秒
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID=5
大于数值
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID>5
更少的价值发现
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID=5