521 votos

¿Cómo puedo limitar el número de filas devueltas por una consulta del Oracle después del pedido?

¿Hay alguna forma de hacer que una consulta de Oracle se comporte como si contuviera un MySQL limit cláusula?

En MySQL, puedo hacer esto:

select * 
from sometable
order by name
limit 20,10

para pasar de la 21ª a la 30ª fila (saltarse las primeras 20, dar las siguientes 10). Las filas se seleccionan después de la order by así que realmente comienza en el vigésimo nombre por orden alfabético.

En el Oracle, lo único que la gente menciona es el rownum pseudo-columna, pero se evalúa antes de order by lo que significa esto:

select * 
from sometable
where rownum <= 10
order by name

devolverá un conjunto aleatorio de diez filas ordenadas por nombre, que no suele ser lo que quiero. Tampoco permite especificar un desplazamiento.

571voto

Kosi2801 Puntos 9487

Puedes usar una subconsulta para esto como

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Echa también un vistazo al tema Sobre ROWNUM y la limitación de los resultados en Oracle/AskTom para más información.

Actualización : Para limitar el resultado con los límites inferiores y superiores las cosas se hinchan un poco más con

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 del artículo AskTom especificado)

129voto

zeldi Puntos 2065

Hice algunas pruebas de rendimiento entre:

A.) Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statemenet with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

B.) Enfoque analítico

select * from (
  <select statemenet with order by clause>
) where myrow between MIN_ROW and MAX_ROW

C.) Alternativa corta

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Resultados:

La tabla tenía 10 millones de registros, la clasificación estaba en una fila de fecha y hora sin indexar:

  • Explica que el plan mostró el mismo valor para las tres selecciones (323168)
  • Pero el ganador es AskTom (con seguimiento analítico de cerca)

Seleccionando las primeras 10 filas tomadas:

  • AskTom: 28-30 segundos
  • Analítico: 33-37 segundos
  • Alternativa corta: 110-140 segundos

Seleccionando filas entre 100.000 y 100.010:

  • AskTom: 60 segundos
  • Analítico: 100 segundos

Seleccionando filas entre 9.000.000 y 9.000.010:

  • AskTom: 130 segundos
  • Analítico: 150 segundos

41voto

Leigh Riffel Puntos 2550

Una solución analítica con una sola consulta anidada:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank() podría ser sustituido por Row_Number() pero podría devolver más registros de los esperados si hay valores duplicados para el nombre.

19voto

beldaz Puntos 1432

En el Oracle 12c (véase la cláusula de limitación de filas en Referencia SQL ):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

5voto

Menos declaraciones de SELECT. También, menos consumo de rendimiento. Créditos a: anibal@upf.br

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X