de Martin LE TARNEC.

Paginar a través del resultado


Después de implementar una sentencia top-N en pipeline para proporcionar de manera eficiente los resultados de la primera página, se necesitará otra sentencia para recuperar los datos de las siguientes páginas. El problema es que se tienen que omitir los registros de las páginas anteriores. Existen dos métodos diferentes para alcanzar este desafío. El primero, el método offset, suma los registros desde el principio y usa un filtro sobre este número para descartar los registros anteriores a la página solicitada. El segundo método, que podría llamarse método de búsqueda, busca la última entrada de la página anterior y sólo devuelve los registros siguientes.

Los siguientes ejemplos muestran los métodos offset más ampliamente conocidos. Su principal ventaja es su facilidad de uso, especialmente con las bases de datos que tienen una palabra clave dedicada (offset). Esa palabra se ha tomado dentro del estándar SQL como parte de la extensión fetch first

DB2

DB2 no soporta la sintaxis estándar offset. El único estándar que da una alternativa es la función ROW_NUMBER() (ver la siguiente sección). Existen otras dos posibilidades para obtener la funcionalidad del offset, pero ninguna de ellas es recomendable: (1) usar db2set DB2_COMPATIBILITY_VECTOR=MYS para habilitar limit y offset como lo soporta MySQL; sin embargo, eso no permite combinar fetch first con offset; (2) usar db2set DB2_COMPATIBILITY_VECTOR=ORA para obtener la seudo columna de Oracle ROWNUM (ver el ejemplo de Oracle).

MySQL

MySQL y PostgreSQL ofrecen la cláusula offset para descartar el número específico de registros desde el inicio de la sentencia top-N. La cláusula limit se aplica posteriormente.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 LIMIT 10 OFFSET 10
Oracle

La base de datos Oracle provee la seudo columna ROWNUM que suma automáticamente los registros dentro del conjunto del resultado. Sin embargo, no es posible aplicar un filtro “superior o igual” (>=) o igual sobre esta seudo columna. Para hacer el trabajo se requiere primero “materializar” el número de registros renombrando la columna con un alias.

SELECT *
  FROM ( SELECT tmp.*, rownum rn
           FROM ( SELECT *
                    FROM sales
                   ORDER BY sale_date DESC
                ) tmp
          WHERE rownum <= 20
       )
 WHERE rn > 10

Observa el uso de los alias RN para el límite inferior y la seudo columna ROWNUM para el límite superior (gracias a Tom Kyte).

PostgreSQL

La extensión fetch first define una cláusula offset ... rows. Sin embargo, PostgreSQL acepta solamente el offset sin la palabra clave rows. La sintaxis anteriormente usada limit/offset también funciona, tal y como se ha visto en el ejemplo de MySQL.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
OFFSET 10
 FETCH NEXT 10 ROWS ONLY
SQL Server

SQL Server no tiene una extensión “offset” para su cláusula top pero se introdujo la extensión fetch first con la versión SQL Server 2012 (“Denali”). La cláusula offset es obligatoria aunque el estándar lo define como opcional.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY

Además de fácil de usar, otra ventaja de este método es que solamente requiere especificar el intervalo de los registros para recuperar los datos de una página aleatoria. Sin embargo, la base de datos debe contar todos los registros desde el principio hasta que llegue a la página solicitada. La Figura 7.2 muestra que el rango escaneado del índice llega a ser más grande cuando se recuperan más páginas.

Figura 7.2 El acceso usando el método Offset

Esto tiene dos desventajas: (1) las páginas se desplazan cuando se insertan nuevas ventas porque la numeración de las páginas se realiza siempre desde cero; (2) el tiempo de respuesta se incrementa cuando se navega más atrás.

El método por búsqueda evita ambos problemas porque usa los valores de la página anterior como delimitador. Eso significa que se buscan los valores que deben venir antes de la última entrada desde la página anterior. Eso se puede expresar con un filtro where sencillo. Dicho de otro modo, el método por búsqueda sencilla ya no selecciona los valores mostrados.

El siguiente ejemplo muestra el método por búsqueda. Para simplificar esta demostración empezamos con la suposición de que existe solamente una venta por día. Esto convierte a la columna SALE_DATE como clave única. Para seleccionar las ventas que deben venir después de una fecha específica, se debe usar la condición menor que (<) debido al orden de clasificación descendente. Para un orden ascendente, se tiene que emplear la condición mayor que (>). La cláusula fetch first se utiliza solamente para limitar el resultado a diez registros.

SELECT *
  FROM sales
 WHERE sale_date < ?
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY

En lugar de usar el número de registros, se usa el último valor de la página anterior par especificar el límite inferior. Eso es un beneficio considerable en términos de rendimiento porque la base de datos puede usar la condición SALE_DATE < ? para tener acceso al índice. Eso significa que la base de datos puede descartar perfectamente los registros de la páginas anteriores. Y además, se obtendrán resultados estables aunque se inserten nuevos registros.

Sin embargo, ese método no funciona si existe más de un solo registro por día (como se ha visto en la Figura 7.2) porque usar la última fecha desde la primera página ("ayer"), obliga a ignorar todos los resultados desde ayer, y no solamente los que se han mostrado en la primera página. El problema es que la cláusula order by no da lugar a una secuencia de filas determinista. Sin embargo, es un prerequisito para usar una condición de rango sencilla para los cambios de página.

Sin una cláusula order by determinista, por definición, la base de datos no entrega una secuencia consistente de filas. La única manera para que se obtenga repetidamente una secuencia de filas consistente es que la base de datos ejecute siempre la sentencia de la misma manera. Sin embargo, la base de datos podrá reorganizar los registros haciendo el mismo SALE_DATE y realizando la cláusula order by. En las versiones recientes, puede ocurrir que se obtenga el resultado en un orden diferente cada vez que se ejecuta la sentencia, no porque la base de datos reorganiza intencionalmente el resultado sino porque la base de datos puede utilizar la ejecución paralela de las sentencias. Eso significa que el mismo plan de ejecución puede generar secuencias de filas diferentes porque la ejecución de los threads terminan en un orden no determinista.

Importante

Paginar requiere un orden de clasificación determinista.

Aun si las especificaciones funcionales sólo requieren una clasificación "por fecha, últimas fechas", debemos, como desarrolladores, asegurarnos de que la cláusula order by genere una secuencia de filas consistente. Para este propósito, se puede necesitar extender la cláusula order by con unas columnas adicionales solamente para asegurarnos de obtener una secuencia de registros consistente. Si el índice que se usa para el order by en pipeline tiene columnas adicionales, es un buen inicio agregarlas a la cláusula order by y así se podrá seguir usando el índice para el order by en pipeline. Si no se genera un orden de clasificación determinista, se agrega solamente una(s) columna(s) única(s) y se extiende el índice en consecuencia.

En el siguiente ejemplo, se extiende la cláusula order by y el índice con la llave primaria SALE_ID para obtener una secuencia de registros consistente. Además, se debe aplicar una lógica de "venir después" sobre ambas columnas juntas para obtener el resultado deseado:

CREATE INDEX sl_dtid ON sales (sale_date, sale_id)
SELECT *
  FROM sales
 WHERE (sale_date, sale_id) < (?, ?)
 ORDER BY sale_date DESC, sale_id DESC
 FETCH FIRST 10 ROWS ONLY

El filtro where usa la sintaxis poco conocida de “fila-valor” (en inglés, row values); ver el apartado llamado Valores de registros SQL). Combina múltiples valores dentro de una lógica única que se aplica a los operadores de comparación regulares. Como con valores escalares, la condición “menos que” corresponde a un "venir después" cuando se ordena de manera descendente. Eso significa que la sentencia considera solamente las ventas que llegan después de la combinación SALE_DATE, SALE_ID dada.

Valores de registros SQL

A parte de los valores escalares regulares, el estándar SQL también define el denominado constructores de valores de línea. Se "especifican un conjunto de valores ordenados para ser construido dentro de una fila o una fila parcial" [SQL:92, §7.1: <row value constructor>]. Sintácticamente, los valores de fila son listas entre paréntesis. Esta sintaxis es mejor conocida por su uso en la sentencia insert.

Sin embargo, usar los constructores de valores de filas en el filtro where es menos conocido pero perfectamente válido. En realidad, el estándar SQL define todos los operadores de comparación para los constructores de valores de filas. La definición comparativa de los valores de filas para la operación "menos que" es, por ejemplo, como sigue:

"Rx < Ry" es verdadero solamente si RXi = RYi para i < n y RXn < RYn para algún valor de n.

SQL:92, §8.2.7.2

Cuando i y n reflejan la posición de los índices en las listas, eso significa que un valor de registro RX es menor que RY si cualquier valor de RXn es más pequeño que el correspondiente RYn y que todos los valores de las parejas anteriores son iguales (RXi = RYi; por ejemplo i<n).

Esa definición produce la expresión RX < RY sinónimo de los "ordenamientos de RX antes de RY" lo que es exactamente la lógica que se necesita para el método de búsqueda.

A pesar de que la sintaxis de los valores de filas es parte del estándar SQL, pocas bases de datos lo soportan. SQL Server 2012 (“Denali”) no soporta los valores de registros completamente. La base de datos Oracle soporta la funcionalidad básica de los valores de filas pero no se puede aplicar para los operadores de rangos sobre ellos (ORA-01796). MySQL evalúa las expresiones de los valores de registros correctamente pero no los usa como predicado de acceso durante un acceso al índice. En contraposición, DB2 (solamente LUW, desde la versión 10.1) y PostgreSQL (desde la versión 8.4) tienen un soporte correcto de los predicados sobre los valores de registros y y los usan como acceso del índice si existe un índice correspondiente disponible.

Sin embargo, es posible usar una variante de aproximación del método por búsqueda con las bases de datos que no soportan correctamente los valores de registros, aunque la aproximación no es elegante y eficiente como los valores de filas en PostgreSQL. Para esta aproximación, se deben usar comparaciones "regulares" con el fin de expresar la lógica requerida, como en este ejemplo de Oracle:

SELECT *
  FROM ( SELECT *
           FROM sales
          WHERE sale_date <= ?
            AND NOT (sale_date = ? AND sale_id >= ?)
          ORDER BY sale_date DESC, sale_id DESC
       )
 WHERE rownum <= 10

El filtro where consiste en dos partes. La primera parte considera solamente SALE_DATE y usa una condición menor o igual (<=) que; selecciona más registros de los que se necesitan. Esta parte del filtro where es suficientemente sencilla, así que todas las bases de datos pueden usarlo como acceso al índice. La segunda parte del filtro where elimina el exceso de registros que ya se han mostrado en la página anterior. El apartado Indexar con la lógica equivalente explica por qué el filtro where se expresa de esta manera.

Indexar con la lógica equivalente

Una condición lógica siempre puede expresarse de diferentes maneras. Por ejemplo, también se podría implementar lo visto arriba omitiendo la lógica:

WHERE (
         (sale_date < ?)
       OR
         (sale_date = ? AND sale_id < ?)
      )

Esta variante solamente usa condiciones incluidas y es probablemente más fácil de entender, por lo menos para seres humanos. Las bases de datos tienen un punto de vista diferente. No reconocen que el filtro where selecciona todos los registros empezando con su pareja respectiva de SALE_DATE/SALE_ID, dado que SALE_DATE es lo mismo por ambas ramas. En cambio, la base de datos usa el filtro where entero como filtro de predicado. Por lo menos, se podría esperar que el optimizador factorice la condición SALE_DATE <= ? de las dos ramas "O" (del inglés OR), pero ninguna base de datos lo hace.

Sin embargo, se puede agregar manualmente una condición redundante, a pesar de que no mejorará la legibilidad:

WHERE sale_date <= ?
  AND (
         (sale_date < ?)
       OR
         (sale_date = ? AND sale_id < ?)
      )

Por suerte, todas las bases de datos son capaces de utilizar la parte del filtro where como acceso de predicado. Sin embargo, este filtro es aún más difícil de entender debido a la lógica aproximativa como se ha visto arriba. Además, la lógica original evita el riesgo de que la parte "innecesaria" (redundante) se elimine de forma accidental desde el filtro where.

El plan de ejecución muestra que la base de datos usa la primera parte del filtro where como predicado de acceso.

---------------------------------------------------------------
|Id | Operation                      | Name    |  Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT               |         |    10 |    4 |
|*1 |  COUNT STOPKEY                 |         |       |      |
| 2 |   VIEW                         |         |    10 |    4 |
| 3 |    TABLE ACCESS BY INDEX ROWID | SALES   | 50218 |    4 |
|*4 |     INDEX RANGE SCAN DESCENDING| SL_DTIT |     2 |    3 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - access("SALE_DATE"<=:SALE_DATE)
       filter("SALE_DATE"<>:SALE_DATE
           OR "SALE_ID"<TO_NUMBER(:SALE_ID))

Los predicados de acceso sobre SALE_DATE permiten a la base de datos omitir los días que se han mostrado ya en páginas anteriores. La segunda parte del where es solamente un filtro de predicado. Eso significa que la base de datos vuelve a inspeccionar algunas entradas de la página anterior, pero las descarta de inmediato. La Figura 7.3 muestra los caminos de acceso respectivos.

Figura 7.3 El acceso usando el método por búsqueda

La Figura 7.4 compara las características de offset y de los métodos por búsqueda. La precisión de las medidas es insuficiente para ver la diferencia sobre la parte izquierda de la gráfica. Sin embargo, la diferencia es claramente visible desde aproximadamente 20 páginas hacia adelante.

Figura 7.4 Escalabilidad cuando se recuperan los datos de la siguiente página

Por supuesto, el método por búsqueda tiene desventajas, y su difícil manejo es uno de los más importantes. Por una parte, es imprescindible escribir cuidadosamente el filtro where; por otra, no vale para obtener los datos de una página aleatoria. Es más, requiere invertir todas las operaciones de comparación y ordenamiento para cambiar el sentido de la navegación. Afortunadamente, estas dos funciones (omitir las páginas y navegar hacia atrás) son innecesarias cuando se usa el mecanismo de la barra de desplazamiento infinita para la interfaz de usuario.

Si te gusta mi manera de explicar, te encantará mi libro.

Figura 7.5 Base de datos/matriz de características

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia