Como limitar o número de linhas retornadas por uma consulta Oracle após a encomenda?
existe uma maneira de fazer uma consulta Oracle
comportar-se como se contivesse uma cláusula MySQL limit
?
em MySQL
, posso fazer isto:
select *
from sometable
order by name
limit 20,10
para obter a 21ª a 30ª filas (pular as primeiras 20, dar as próximas 10). As linhas são seleccionadas a seguir ao order by
, por isso começa alfabeticamente com o 20º nome.
em Oracle
, a única coisa que as pessoas mencionam é a pseudo-coluna rownum
, mas é avaliada antes order by
, o que significa isto:
select *
from sometable
where rownum <= 10
order by name
vai devolver um conjunto aleatório de dez linhas ordenadas pelo nome, que normalmente não é o que eu quero. Também não permite especificar uma compensação.
4 answers
A partir do Oracle 12c R1 (12.1), existe Uma cláusula limitativa da linha . Não usa sintaxe familiar LIMIT
, mas pode fazer melhor o trabalho com mais opções. Você pode encontrar a sintaxe completa aqui .
Para responder à pergunta original, aqui está a pergunta:
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
(para versões Oracle anteriores, por favor consulte outras respostas nesta pergunta)
Exemplos:
A seguir foram citados exemplos de ligados page , na esperança de evitar o rot de ligação.
Configuração
CREATE TABLE rownum_order_test (
val NUMBER
);
INSERT ALL
INTO rownum_order_test
SELECT level
FROM dual
CONNECT BY level <= 10;
COMMIT;
O que está na mesa?
SELECT val
FROM rownum_order_test
ORDER BY val;
VAL
----------
1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
10
20 rows selected.
Primeiro N
filas
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;
VAL
----------
10
10
9
9
8
5 rows selected.
Primeiro N
filas, se N
a linha tem gravatas, todas as linhas empatadas.
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;
VAL
----------
10
10
9
9
8
8
6 rows selected.
Top x
% das linhas
SELECT val
FROM rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;
VAL
----------
1
1
2
2
4 rows selected.
Usando um offset, muito útil para paginação
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
VAL
----------
3
3
4
4
4 rows selected.
Pode combinar compensação com percentagens
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;
VAL
----------
3
3
4
4
4 rows selected.
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;
VAL
----------
10
10
9
9
8
8
6 rows selected.
x
% das linhas SELECT val
FROM rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;
VAL
----------
1
1
2
2
4 rows selected.
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
VAL
----------
3
3
4
4
4 rows selected.
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;
VAL
----------
3
3
4
4
4 rows selected.
Podes usar um subcontingente para isto como
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Veja também o tópico sobre o ROWNUM e os resultados limitativos na Oracle / AskTom para mais informações.
Actualizar : Para limitar o resultado com os limites inferior e superior as coisas ficam um pouco mais inchadas com
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;
(copiado do artigo asktom especificado)
Actualizar 2 : Começando com Oracle 12c (12.1) existe uma sintaxe disponível para limitar as linhas ou começar em deslocamento.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Veja esta resposta para mais exemplos. Graças ao Krumia pela dica.
Asktom
select * from (
select a.*, ROWNUM rnum from (
<select statement with order by clause>
) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW
Analítico
select * from (
<select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW
Pequena Alternativa
select * from (
select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW
Resultados
A tabela tinha 10 milhões de Registos, o sort estava numa linha de datetime não indexada:
- O Plano de explicação mostrou o mesmo valor para todas as três seleções (323168)
- Mas o vencedor é AskTom (com o analítico atrás)
A selecção das primeiras 10 linhas levou:
- AskTom: 28-30 segundos
- analítico: 33-37 segundos
- pequena alternativa: 110-140 segundos
Seleccionar linhas entre 100.000 e 100.010:
- AskTom: 60 segundos
- analítico: 100 segundos
- AskTom: 130 segundos
- analítico: 150 segundos
Uma solução analítica com apenas uma consulta aninhada:
SELECT * FROM
(
SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
)
WHERE MyRow BETWEEN 10 AND 20;
Rank()
pode ser substituído por Row_Number()
mas pode devolver mais registos do que espera se existirem valores duplicados para o nome.