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.

Author: DineshDB, 0000-00-00
Source

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 Na 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.

 351
Author: Krumia, 2017-01-03 05:03:14

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.

 723
Author: Kosi2801, 2014-10-25 19:18:07
Fiz alguns testes de desempenho para as seguintes abordagens:

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
Seleccionando linhas entre 9,000,000 e 9,000,010:
  • AskTom: 130 segundos
  • analítico: 150 segundos
 169
Author: zeldi, 2018-06-29 09:26:59

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.

 53
Author: Leigh Riffel, 2016-01-14 10:52:18