¿Cómo puedo limitar el número de filas devueltas por una consulta de Oracle después de realizar el pedido?
¿Hay alguna manera de hacer que una consulta Oracle se comporte como si contuviera una cláusula MySQL limit?
En MySQL, puedo hacer esto:
select *
from sometable
order by name
limit 20,10
Para obtener las filas 21 a 30 (omita las primeras 20, dé las siguientes 10). Las filas se seleccionan después de order by, por lo que realmente comienza en el nombre 20 alfabéticamente.
En Oracle, lo único que la gente menciona es la pseudo-columna rownum, pero se evalúa antes 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 normalmente no es lo que quiero. Tampoco permite especificar un desplazamiento.
4 answers
A partir de Oracle 12c R1 (12.1), hay esuna cláusula límite de fila . No utiliza la sintaxis familiar LIMIT, pero puede hacer el trabajo mejor con más opciones. Puede encontrar la sintaxis completa aquí.
Para responder a la pregunta original, aquí está la pregunta:
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:
Se citaron los siguientes ejemplos de vinculados page , con la esperanza de evitar la putrefacción de enlaces.
Configuración
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 tabla?
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 empates, obtener 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 el 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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-01-03 05:03:14
Puedes usar una subconsulta para esto como
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
También eche un vistazo al tema En ROWNUM y limiting results en Oracle/AskTom para obtener más información.
Actualización : Para limitar el resultado con los límites inferior y superior cosas conseguir un poco más hinchado 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)
Actualización 2: A partir de Oracle 12c (12.1) hay una sintaxis disponible para limitar filas o comenzar en desplazamientos.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Véase esta respuesta para más ejemplos. Gracias a Krumia por la pista.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-10-25 19:18:07
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, sort estaba en una fila datetime sin indicar:
- Explain plan mostró el mismo valor para los tres selects (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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-06-29 09:26:59
Una solución analítica con solo una 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 name.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-01-14 10:52:18