¿Cómo funciona ROWNUM en la consulta de paginación?
Entonces quiero seleccionar un rango de filas en una base de datos Oracle. Necesito hacer esto porque tengo millones de filas en la tabla y quiero paginar los resultados para el usuario (si conoce otra forma de hacer esto en el lado del cliente, estoy usando JavaFX si es importante, pero no Creo que es una buena idea enviar todos los datos a través de la red para paginarlos en el lado del cliente).
Entonces, después de leer esta publicación: SQL ROWNUM cómo devolver filas entre un rango específico , tengo la siguiente consulta:
Select * From (Select t.*, rownum r from PERSON t) Where r > 100 and r < 110;
Los 100y 110son solo un ejemplo. En la aplicación, simplemente pido el límite inferior y agrego un tamaño de 10_000 para recuperar las siguientes 10_000 filas.
Ahora la columna Rownum aparece en el resultado y no quiero verla. Como no tengo mucha experiencia con SQL, aquí están mis preguntas:
¿Por qué (este fue mi primer intento hasta que busqué en SO)
Select * From Person Where rownum > 100 and rownum < 110;devuelve 0 filas?¿ Por qué no existe una forma sencilla de hacer algo como
Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound?¿ Cómo deshacerse de la
rcolumna en los valores resultantes? Desde allí, SQL excluye una columna usando SELECT * [excepto columnaA] FROM tableA. Aparentemente necesito crear una vista o una tabla temporal, pero ¿hay otra manera de considerar mi consulta?¿Asegura una paginación correcta? Leí la sección "Paginación con ROWNUM" de este artículo , que decía que debería ordenar los valores por algo único para obtener una paginación consistente (así que supongo que ordenar por
rownumestá bien, si puedes confirmarlo). ¿No anula el propósito de su usoFIRST_ROWS(N)?
Espero que no sea demasiado, podría dividirlas en preguntas separadas, pero creo que es relevante colapsarlas ya que están estrechamente relacionadas.
Tiene 4 preguntas y todas giran en torno al uso y la funcionalidad de ROWNUM . Responderé cada pregunta una por una.
Por qué (este fue mi primer intento hasta que busqué en SO) Seleccionar * De persona donde rownum > 100 y rownum < 110; devuelve 0 filas?
Buena explicación de Thomas Kyte sobre ROWNUM y paginación aquí .
Se asigna un valor ROWNUM a una fila después de que pasa la fase de predicado de la consulta, pero antes de que la consulta realice alguna clasificación o agregación. Además, un valor ROWNUM se incrementa solo después de asignarlo, razón por la cual la siguiente consulta nunca devolverá una fila:
select *
from t
where ROWNUM > 1;
Debido a que ROWNUM > 1 no es cierto para la primera fila, ROWNUM no avanza a 2. Por lo tanto, ningún valor de ROWNUM llega a ser mayor que 1.
¿Por qué no existe una forma sencilla de hacer algo como Seleccionar... DESDE... DONDE rownum ENTRE límite inferior Y límite superior?
Sí hay. Desde Oracle 12c en adelante, puede utilizar la nueva función de limitación Top-n Row . Vea mi respuesta aquí .
Por ejemplo, la siguiente consulta devolvería los empleados entre el cuarto y el séptimo salario más alto en orden ascendente:
SQL> SELECT empno, sal
2 FROM emp
3 ORDER BY sal
4 OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
EMPNO SAL
---------- ----------
7654 1250
7934 1300
7844 1500
7499 1600
SQL>
¿Cómo deshacerse de la columna r en los valores resultantes?
En lugar de select *, enumere los nombres de las columnas requeridas en la consulta externa. Para el uso frecuente de la consulta, crear una vista es una actividad sencilla que se realiza una vez.
Alternativamente, SQL*Pluspuede utilizar el comando NOPRINT . No mostrará el nombre de la columna que no desea mostrar. Sin embargo, sólo funcionaría en SQL*Plus.
Por ejemplo,
COLUMN column_name NOPRINT
Por ejemplo,
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> COLUMN dname NOPRINT
SQL> COLUMN LOC NOPRINT
SQL> SELECT * FROM dept;
DEPTNO
----------
10
20
30
40
SQL>
¿Asegura una paginación correcta?
Sí, si escribes la consulta de paginación correctamente.
Por ejemplo,
SELECT val
FROM (SELECT val, rownum AS rnum
FROM (SELECT val
FROM t
ORDER BY val)
WHERE rownum <= 8)
WHERE rnum >= 5;
VAL
----------
3
3
4
4
4 rows selected.
SQL>
O utilice la nueva función de limitación de filas en 12c como se muestra arriba.
Algunos buenos ejemplos aquí .
Respuesta a la pregunta 2: En Oracle 12 puedes usar la paginación
select owner, object_name, object_id
from t
order by owner, object_name
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
