sql - registros - select top oracle
¿Cómo limito el número de filas devueltas por una consulta de Oracle después de ordenar? (14)
¿Hay alguna manera de hacer que una consulta de Oracle comporte como si contuviera una cláusula de MySQL limit ?
En MySQL , puedo hacer esto:
select *
from sometable
order by name
limit 20,10
para obtener las filas 21 a 30 (salta las primeras 20, da las siguientes 10). Las filas se seleccionan después del order by , order by lo que realmente comienza en el vigésimo nombre alfabéticamente.
En Oracle , lo único que mencionan las personas es la pseudo-columna de rownum , 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.
(sin probar) algo como esto puede hacer el trabajo
WITH
base AS
(
select * -- get the table
from sometable
order by name -- in the desired order
),
twenty AS
(
select * -- get the first 30 rows
from base
where rownum < 30
order by name -- in the desired order
)
select * -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name -- in the desired order
También está el rango de función analítica, que puede utilizar para ordenar.
A partir de Oracle 12c R1 (12.1), hay una cláusula de limitación de fila . No usa la sintaxis LIMIT familiar, pero puede hacer el trabajo mejor con más opciones. Puedes encontrar la sintaxis completa aquí .
Para responder a 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 la página enlazada , con la esperanza de prevenir la rotura 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 las primeras 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.
Obtén las primeras N filas, si la fila N tiene vínculos, obtén 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.
Top 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 offset, 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.
Puedes combinar offset 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.
Empecé a prepararme para el examen Oracle 1z0-047, validado contra 12c Mientras preparaba para él, encontré una mejora de 12c conocida como ''FETCH FIRST''. Le permite buscar filas / filas de límites según su conveniencia. Varias opciones están disponibles con él.
- 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
En Oracle 12c (ver cláusula de limitación de fila en referencia de SQL ):
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
En oracle
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>
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, ordenados en una fila de fecha y hora no indexada:
- El plan de explicación mostró el mismo valor para los tres seleccionados (323168)
- Pero el ganador es AskTom (con un seguimiento analítico muy cerca)
Seleccionando las primeras 10 filas tomó:
- AskTom: 28-30 segundos
- Analítica: 33-37 segundos.
- Alternativa corta: 110-140 segundos
Seleccionando filas entre 100,000 y 100,010:
- AskTom: 60 segundos
- Analítica: 100 segundos.
Seleccionando filas entre 9,000,000 y 9,000,010:
- AskTom: 130 segundos
- Analítica: 150 segundos.
Igual que el anterior con correcciones. Funciona pero definitivamente no es bonito.
WITH
base AS
(
select * -- get the table
from sometable
order by name -- in the desired order
),
twenty AS
(
select * -- get the first 30 rows
from base
where rownum <= 30
order by name -- in the desired order
)
select * -- then get rows 21 .. 30
from twenty
where rownum < 20
order by name -- in the desired order
Honestamente, mejor usar las respuestas anteriores.
Las consultas de paginación con orden son realmente difíciles 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 unidas.
ROWNUM es una pseudocolumna que causa problemas a muchas personas. Un valor ROWNUM no se asigna permanentemente a una fila (esto es un malentendido común). Puede ser confuso cuando se asigna realmente un valor ROWNUM. Se asigna un valor ROWNUM a una fila después de que pasa los predicados de filtro de la consulta pero antes de la agregación o clasificación de la consulta.
Además, un valor ROWNUM se incrementa solo después de que se asigna.
Es por esto que 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 se incrementa a 2. Por esta razón, ningún valor ROWNUM obtiene más de 1, por lo tanto, la consulta no devuelve filas.
La consulta correctamente definida debería tener este aspecto:
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 en el blog de Vertabelo :
Menos sentencias SELECT. Además, consume menos rendimiento. Créditos a: [email protected]
SELECT *
FROM (SELECT t.*,
rownum AS rn
FROM shhospede t) a
WHERE a.rn >= in_first
AND a.rn <= in_first;
Para cada fila devuelta por una consulta, la pseudocolumna ROWNUM devuelve un número que indica el orden en que Oracle selecciona la fila de una tabla o conjunto de filas unidas. La primera fila seleccionada tiene un ROWNUM de 1, la segunda tiene 2 y así sucesivamente.
SELECT * FROM sometable1 so
WHERE so.id IN (
SELECT so2.id from sometable2 so2
WHERE ROWNUM <=5
)
AND ROWNUM <= 100
He implementado esto en oracle server 11.2.0.1.0
Puedes usar una subconsulta para esto como
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Consulte también el tema En ROWNUM y limite los resultados en Oracle / AskTom para obtener más información.
Actualización : para limitar el resultado con los límites inferior y superior, 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 de un artículo AskTom específico)
Actualización 2 : A partir de Oracle 12c (12.1), hay una sintaxis disponible para limitar filas o comenzar en las compensaciones.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Vea esta respuesta para más ejemplos. Gracias a Krumia por la pista.
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 puede mover esto de cláusula con cláusula 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 utilizar rnum en la consulta principal como criterios de filtro.
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 sustituirse por Row_Number() pero podría devolver más registros de los que espera si hay valores duplicados para el nombre.
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID=5
mayor que los valores descubren
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID>5
menos entonces los valores descubren
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID=5