Velocidad de las consultas paginadas en Oracle

Este es un tema interminable para mí y me pregunto si podría pasar por alto algo. Esencialmente uso dos types de declaraciones SQL en una aplicación:

  1. Consultas regulares con un límite de "repliegue"
  2. Consultas orderadas y paginadas

Ahora, estamos hablando de algunas consultas contra tablas con varios millones de loggings, unidas a 5 tablas más con varios millones de loggings. Claramente, casi no queremos searchlos a todos, por eso tenemos los dos methods anteriores para limitar las consultas de los usuarios.

El caso 1 es realmente simple. Simplemente agregamos un filter ROWNUM adicional:

 WHERE ... AND ROWNUM < ? 

Eso es bastante rápido, ya que la CBO de Oracle tomará en consideración este filter para su plan de ejecución y probablemente aplique una operación FIRST_ROWS (similar a la impuesta por /*+FIRST_ROWS*/ hint.

El caso 2 , sin embargo, es un poco más complicado con Oracle, ya que no existe una cláusula LIMIT ... OFFSET como en otros RDBMS. Entonces anidamos nuestra consulta de "negocios" en un contenedor técnico como tal:

 SELECT outer.* FROM ( SELECT * FROM ( SELECT inner.*, ROWNUM as RNUM, MAX(ROWNUM) OVER(PARTITION BY 1) as TOTAL_ROWS FROM ( [... USER SORTED business query ...] ) inner ) WHERE ROWNUM < ? ) outer WHERE outer.RNUM > ? 

Tenga en count que el campo TOTAL_ROWS se calcula para saber cuántas páginas tendremos incluso sin get todos los datos. Ahora esta consulta de búsqueda es bastante satisfactoria. Pero de vez en cuando (como dije, al consultar loggings de 5M +, posiblemente incluyendo búsquedas no indexadas), esto funciona durante 2-3 minutos.

EDITAR : Tenga en count que un cuello de botella potencial no es tan fácil de sortear, ¡debido a la sorting que debe aplicarse antes de la búsqueda!

Me pregunto, ¿es esa simulación de última generación de LIMIT ... OFFSET , incluyendo TOTAL_ROWS en Oracle, o hay una mejor solución que será más rápida por layout, por ejemplo, utilizando la function de window ROW_NUMBER() lugar de la pseudocolumna ROWNUM ?

El principal problema con el Caso 2 es que en muchos casos se debe get el set completo de resultados de la consulta y luego orderarlos antes de que se puedan devolver las primeras N filas, a less que las columnas ORDER BY estén indexadas y Oracle pueda usar el índice para evitar una orderación. Para una consulta compleja y un gran set de datos, esto puede llevar algo de time. Sin embargo, puede haber algunas cosas que puede hacer para mejorar la velocidad:

  1. Intente asegurarse de que no se invoquen funciones en el SQL interno; pueden llamarse 5 millones de veces solo para devolver las primeras 20 filas. Si puede mover estas llamadas a la consulta externa, se llamarán less.
  2. Use una FIRST_ROWS_n sugerencia para empujar a Oracle a la optimization por el hecho de que nunca devolverá todos los datos.

EDITAR:

Otro pensamiento: actualmente está presentando al usuario un informe que podría devolver miles o millones de filas, pero el usuario nunca va a searchlas de manera realist. ¿No puede obligarlos a seleccionar una cantidad menor de datos, por ejemplo, limitando el intervalo de dates seleccionado a 3 meses (o lo que sea)?

Es posible que desee rastrear la consulta que requiere mucho time y ver su plan de explicación. Lo más probable es que el cuello de botella de performance provenga del cálculo de TOTAL_ROWS. Oracle tiene que leer todos los datos, incluso si solo obtiene una fila, este es un problema común que enfrentan todos los RDBMS con este tipo de consulta. Ninguna implementación de TOTAL_ROWS evitará eso.

La forma radical de acelerar este tipo de consultas es renunciar al cálculo de TOTAL_ROWS. Solo muestra que hay páginas adicionales. ¿Sus usuarios realmente necesitan saber que pueden navegar a través de 52486 páginas? Una estimación puede ser suficiente. Esa es otra solución, implementada por la búsqueda de Google, por ejemplo: estimar el número de páginas en lugar de contarlas realmente.

Diseñar un algorithm de estimación preciso y eficiente puede no ser trivial.

Un "LIMIT … OFFSET" es bastante azúcar sintáctica. Puede hacer que la consulta se vea más bonita, pero si aún necesita leer todo un set de datos, orderarlo y get filas "50-60", ese es el trabajo que debe realizarse.

Si tiene un índice en el order correcto, eso puede ayudar.

Puede funcionar mejor ejecutar dos consultas en lugar de intentar contar () y devolver los resultados en la misma consulta. Oracle puede ser capaz de contestar el conteo () sin orderar o join a todas las tablas (join a la eliminación de tabla basada en restricciones de key externa declaradas). Esto es lo que generalmente hacemos en nuestra aplicación. Para declaraciones de performance importante, escribimos una consulta separada que sabemos devolverá el recuento correcto ya que a veces podemos hacerlo mejor que Oracle.

Alternativamente, puede hacer una compensación entre el performance y la actualidad de los datos. Recuperar las primeras 5 páginas será casi tan rápido como recuperar la primera página. Por lo tanto, podría considerar almacenar los resultados de 5 páginas en una tabla temporal junto con una date de vencimiento para la información. Tome el resultado de la tabla temporal si es válido. Ponga una tarea en segundo plano para eliminar los datos caducados periódicamente.