Pregunta ¿Cómo limito el número de filas devueltas por una consulta de Oracle después de ordenar?


¿Hay alguna manera de hacer un Oracle consulta se comportan como si tuviera un MySQL limit ¿cláusula?

En MySQL, Puedo hacer esto:

select * 
from sometable
order by name
limit 20,10

para obtener las filas 21 a 30 (saltee las primeras 20, dé las 10 siguientes). Las filas se seleccionan después de order by, entonces realmente comienza en el 20º nombre alfabéticamente.

En Oracle, lo único que la gente menciona es el rownum pseudocolumna, 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 generalmente no es lo que quiero. Tampoco permite especificar un desplazamiento.


805
2018-01-22 19:48


origen


Respuestas:


A partir de Oracle 12c R1 (12.1), hay es un cláusula de límite de fila. No usa familiar LIMIT sintaxis, pero puede hacer el trabajo mejor con más opciones. Puedes encontrar el sintaxis completa aquí.

Para responder la pregunta original, aquí está la consulta:

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

(Para versiones anteriores de Oracle, consulte otras respuestas en esta pregunta)


Ejemplos:

Los siguientes ejemplos fueron citados de página enlazada, con la esperanza de prevenir la pudrición del enlace.

Preparar

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

¿Qué hay en la 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.

Obtener primero 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.

Obtener primero N filas, si Nth fila tiene lazos, consigue todas las filas atadas

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.

Parte superior x% de filas

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

Usando un desplazamiento, muy útil para la paginación

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.

Puede combinar desplazamiento con porcentajes

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.

328
2017-09-26 04:01



Puedes usar una subconsulta para esto como

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

Echa un vistazo al tema En ROWNUM y resultados limitantes en Oracle / AskTom para más información.

Actualizar: Para limitar el resultado con los límites inferior y superior, las cosas se vuelven un poco más infladas 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 AskTom-artículo especificado)

Actualización 2: Comenzando con Oracle 12c (12.1) hay una sintaxis disponible para limitar las filas o comenzar en las compensaciones.

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

Ver esta respuesta para más ejemplos. Gracias a Krumia por la pista.


713
2018-01-22 19:55



Hice algunas pruebas de rendimiento para los siguientes enfoques:

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

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, el género estaba en una fila de fecha y hora no indexada:

  • El plan de explicación mostró el mismo valor para las tres selecciones (323168)
  • Pero el ganador es AskTom (con seguimiento analítico detrás)

Seleccionar las primeras 10 filas tomó:

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

Seleccionar filas entre 100,000 y 100,010:

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

Seleccionar filas entre 9,000,000 y 9,000,010:

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

166
2018-06-30 14:20



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 que espera si hay valores duplicados para el nombre.


52
2018-01-23 14:28



En Oracle 12c (vea la cláusula de límite de fila en Referencia de SQL)

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

27
2017-09-24 03:09



Las consultas de paginación con pedidos son realmente complicadas en Oracle.

Oracle proporciona una pseudocolumna ROWNUM que devuelve un número que indica el orden en que la base de datos selecciona la fila de una tabla o conjunto de vistas combinadas.

ROWNUM es una pseudocolumna que tiene muchas personas en problemas. Un valor ROWNUM no está asignado permanentemente a una fila (este es un malentendido común). Puede ser confuso cuando se asigna un valor ROWNUM. Se asigna un valor ROWNUM a una fila después de pasar los predicados de filtro de la consulta pero antes de agregar o clasificar la consulta.

Lo que es más, un valor ROWNUM se incrementa solo después de que se le asigna.

Esta es la razón por la cual la siguiente consulta no devuelve filas:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

La primera fila del resultado de la consulta no pasa el predicado ROWNUM> 1, por lo que ROWNUM no incrementa a 2. Por esta razón, ningún valor de ROWNUM es mayor que 1, por lo tanto, la consulta no devuelve filas.

La consulta correctamente definida debería verse así:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

Obtenga más información sobre las consultas de paginación en mis artículos sobre Vertabelo Blog:


10
2018-04-12 17:32



Menos declaraciones SELECT. Además, consume menos 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;

7
2018-03-02 14:32



Si no está en Oracle 12C, puede usar la consulta TOP N como se muestra a continuación.

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

Incluso puedes mover esto de la cláusula in with clause de la siguiente manera

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

Aquí en realidad estamos creando una vista en línea y cambiando el nombre de rownum como rnum. Puede usar rnum en la consulta principal como criterio de filtro.


3
2017-12-08 10:11



select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

más grande que los valores averiguar

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

menos de los valores se dan cuenta

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

3
2018-04-12 11:01



Empecé a prepararme para el examen Oracle 1z0-047, validado contra 12c Mientras me preparaba encontré una mejora 12c conocida como 'FETCH FIRST' Le permite buscar filas / filas de límite según su conveniencia. Varias opciones están disponibles con esto

- FETCH FIRST n ROWS ONLY
 - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
 - n % rows via FETCH FIRST N PERCENT ROWS ONLY

Ejemplo:

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY

1
2018-06-01 10:31



En el oráculo

SELECT val FROM   rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY;

     VAL

    10
    10
     9
     9
     8

5 filas seleccionadas

SQL>


-2
2017-08-20 18:33