Paginar a través del resultado

Después de implementar una sentencia top-N en pipeline para proporcio­nar 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.

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.

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.

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 co­lumnas 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.

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 2017 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 (LUW) y PostgreSQL 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 suficiente­mente 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.

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

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

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 des­carta de inmediato. La Figura 7.3 muestra los caminos de acceso respectivos.

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.

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