sql - sentencia - ¿Cómo funciona ROWNUM en la consulta de paginación?

sentencia top sql

Question

Entonces quiero seleccionar un rango de filas en un Oracle DB. Necesito hacer esto porque tengo millones de filas en la tabla y quiero paginar los resultados al 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 100 y 110 son solo un ejemplo. En la aplicación, solo solicito el límite inferior y agrego un tamaño de 10_000 para obtener las siguientes 10_000 filas.

Ahora aparece la columna rownum en el resultado y no quiero verla. Como no tengo mucha experiencia con SQL, estas son mis preguntas:

1. Por qué (este fue mi primer intento hasta que busque en SO) Select * From Person Where rownum > 100 and rownum < 110; devuelve 0 filas?

2. ¿Por qué no hay una manera sencilla de hacer algo como Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound ?

3. ¿Cómo deshacerse de la columna r en los valores resultantes? A partir de ahí, SQL excluye una columna usando SELECT * [excepto la columna A] FROM tableA? Aparentemente necesito crear una vista o una tabla temporal, pero ¿hay otra forma de considerar mi consulta?

4. ¿Asegura la paginación correcta? Leí la sección de este artículo "Paginación con ROWNUM", que decía que debería ordenar los valores por algo único para obtener una paginación consistente (así que supongo que ordenar por rownum está bien, si puedes confirmarlo). ¿No vence el propósito de usar FIRST_ROWS(N) ?

Espero que no sea demasiado, podría dividirme en preguntas separadas, pero creo que es relevante colapsarlas ya que están estrechamente relacionadas.

Gracias :)

Answer 1

Normalmente escribo una consulta como esta:

select * 
from 
(
    select a.*, rownum as rn  
    from table_name a
    where rownum <= 110
)
where rn > 100 

Answer 2

Respuesta a la pregunta 2: en Oracle 12 puede 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;

Answer 3

Tiene 4 preguntas y todas giran en torno al uso y la funcionalidad de ROWNUM . Contestaré cada pregunta una por una.

Por qué (este fue mi primer intento hasta que busque en SO) Seleccione * De la 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 una clasificación o agregación. Además, un valor ROWNUM se incrementa solo después de que se asigna, por lo que 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 hay una manera sencilla de hacer algo como Seleccionar ... DESDE ... DONDE rownum ENTRE lowerBound Y upperBound?

Sí hay. Desde Oracle 12c en adelante, puede usar la nueva función de limitación de Top-n Row . Mira mi respuesta aquí .

Por ejemplo, la consulta a continuación devolvería 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 columna necesarios en la consulta externa. Para utilizar la consulta con frecuencia, crear una vista es una actividad simple y única.

Alternativamente, en SQL*Plus puede usar el comando NOPRINT . No mostrará el nombre de la columna que no desea mostrar. Sin embargo, solo 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 la paginación correcta?

Sí, si escribe 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 use la nueva función de limitación de filas en 12c como he mostrado anteriormente.

Pocos buenos ejemplos here .