Al venir de otros manejadores de base de datos, como por ejemplo Mysql, Postgres, SQLite, ya conocemos la forma de limitar registros con la clausula LIMIT, dicha clausula no existe en Oracle (tampoco en SQLServer) y siempre ha sido una polémica entre los usuarios por lo importante que es.
Cómo ya estamos acostumbrados, lo consideramos como parte del estándar pero no lo es, no se contempla entre los estándares de SQL, así que es común que otros manejadores no tengan esta opción.
La primera vez que necesité esta opción con Oracle, duré varias horas buscando la alternativa adecuada. Hay varias maneras de limitar pero su comportamiento es distinto.
Ejemplo, tenemos una simple tabla Libros con dos columnas id y titulo. Para limitar los registros en un select, podemos usar la clausula ROWNUM, la cual genera un conteo de los registros, le asigna un número al resultado y luego lo utilizamos.
Simple Limit
|
1 |
SELECT * FROM libros WHERE ROWNUM <= 3; |
Con la anterior consulta limitamos a 3 registros, algo simple y rápido pero tiene un detalle a la hora de ordenar, ya que ROWNUM ocurre antes del ordenamiento. Esto significa que tomará registros aleatorios, ejecutará el límite y luego ordenará lo resultante. Esto es completamente al revés en otros manejadores, donde ordena primero y luego ejecuta el límite.
Limit con ordenamiento (Problema)
|
1 2 3 |
SELECT * FROM libros WHERE ROWNUM <= 3 ORDER BY titulo ASC; |
Ya uses ASC o DESC, siempre traerá los mismos registros, es decir, en otros manejadores el order by, te trae o los primeros 3 registros de la tabla o los 3 últimos registros de la tabla pero en Oracle no, en Oracle te trae los mismos registros siempre y luego los ordena.
Para evitar este inconveniente se debe usar el conteo ROWNUM después y la única forma es utilizando subquery donde la consulta superior se encargará de ordenar:
Limit y con ordenamieto similar a Mysql, Postgres
|
1 2 3 4 |
SELECT * FROM ( SELECT a.* FROM libros a ORDER BY titulo ASC ) WHERE ROWNUM BETWEEN 1 AND 3; |
El anterior utiliza between pero también podemos usar las comunes «mayor igual» y «menor igual»:
|
1 2 3 4 |
SELECT * FROM ( SELECT a.* FROM libros a ORDER BY a.titulo ASC ) WHERE ROWNUM >=1 AND ROWNUM <=3; |
Compleja pero rápida
En internet se encontrará muchas más formas, anidadas, complejas y la más optimizada por la velocidad es esta:
|
1 2 3 4 5 |
SELECT * FROM ( SELECT a.*, ROWNUM AS num FROM ( SELECT * FROM libros ORDER BY titulo ASC ) a WHERE ROWNUM <= 3 ) WHERE num >= 1 |
Aunque no me agrada tanta anidación, cuando trabajas con millones de registros, es la más rápida.
Limitando con Offset
Otra forma implementada a partir de Oracle 12C R1 (12.1), es la clausula OFFSET, algo más familiar y que nos permite realizar paginación fácilmente:
|
1 2 3 |
SELECT * FROM libros ORDER BY titulo OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; |
Donde indica que después de las primeras 20 filas, tomará las siguientes 10. Bastante útil y aunque el offset es familiar, el fetch next only es algo completamente nuevo y difiere de los demás manejadores, por lo tanto debemos acostumbrarnos que en Oracle todo es y seguirá siendo diferente en comparación con los demás, complicando la estandarización de nuestras consultas.
