有没有办法使一个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行按名称排序的集合，这通常不是我想要的。它也不允许指定偏移量。
当前回答
我为以下方法做了一些性能测试:
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秒
其他回答
在Oracle 12c上(参见SQL参考中的行限制子句):
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
使用21c版本，您可以简单地应用限制如下:
select * from course where ROWNUM <=10;
SQL标准
从版本12c开始，Oracle支持SQL:2008标准，该标准提供了以下语法来限制SQL结果集:
SELECT
title
FROM
post
ORDER BY
id DESC
FETCH FIRST 50 ROWS ONLY
Oracle 11g及更老版本
在版本12c之前，要获取Top-N记录，必须使用派生表和ROWNUM伪列:
SELECT *
FROM (
SELECT
title
FROM
post
ORDER BY
id DESC
)
WHERE ROWNUM <= 50
在Oracle中，带有排序的分页查询非常棘手。
Oracle提供了一个ROWNUM伪列，它返回一个数字，指示数据库从表或连接视图集中选择行的顺序。
ROWNUM是一个伪列，它会给很多人带来麻烦。ROWNUM值不会永久地分配给行(这是一个常见的误解)。当实际分配ROWNUM值时，可能会令人困惑。ROWNUM值在行传递查询的过滤器谓词之后，但在查询聚合或排序之前分配给该行。
而且，ROWNUM值只有在赋值之后才会递增。
这就是为什么下面的查询不返回行:
select *
from (select *
from some_table
order by some_column)
where ROWNUM <= 4 and ROWNUM > 1;
查询结果的第一行没有传递ROWNUM > 1谓词，因此ROWNUM不增加到2。因此，没有ROWNUM值大于1，因此，查询不返回任何行。
正确定义的查询应该是这样的:
select *
from (select *, ROWNUM rnum
from (select *
from skijump_results
order by points)
where ROWNUM <= 4)
where rnum > 1;
在Vertabelo博客上的文章中可以找到更多关于分页查询的信息:
Oracle ROWNUM解释 Top-N和分页查询
作为公认答案的扩展，Oracle内部使用ROW_NUMBER/RANK函数。OFFSET FETCH语法是语法糖。
可以使用DBMS_UTILITY来观察。EXPAND_SQL_TEXT过程:
准备样例:
CREATE TABLE rownum_order_test (
val NUMBER
);
INSERT ALL
INTO rownum_order_test
SELECT level
FROM dual
CONNECT BY level <= 10;
COMMIT;
查询:
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;
常规:
SELECT "A1"."VAL" "VAL"
FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber"
FROM "ROWNUM_ORDER_TEST" "A2") "A1"
WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;
db < > fiddle演示
获取展开的SQL文本:
declare
x VARCHAR2(1000);
begin
dbms_utility.expand_sql_text(
input_sql_text => '
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY',
output_sql_text => x);
dbms_output.put_line(x);
end;
/
WITH TIES扩展为RANK:
declare
x VARCHAR2(1000);
begin
dbms_utility.expand_sql_text(
input_sql_text => '
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES',
output_sql_text => x);
dbms_output.put_line(x);
end;
/
SELECT "A1"."VAL" "VAL"
FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank"
FROM "ROWNUM_ORDER_TEST" "A2") "A1"
WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC
和偏移量:
declare
x VARCHAR2(1000);
begin
dbms_utility.expand_sql_text(
input_sql_text => '
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
output_sql_text => x);
dbms_output.put_line(x);
end;
/
SELECT "A1"."VAL" "VAL"
FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber"
FROM "ROWNUM_ORDER_TEST" "A2") "A1"
WHERE "A1"."rowlimit_$$_rownumber"<=CASE WHEN (4>=0) THEN FLOOR(TO_NUMBER(4))
ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4
ORDER BY "A1"."rowlimit_$_0"
