es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

EclipseLink con Oracle: “limit por rownum” no utiliza el índice.

15 Jul, 2023 Programación

Nos enfrentamos a problemas de rendimiento con EclipseLink 2.7.7 al acceder a tablas de Oracle 12.1 con paginación. La investigación mostró que Oracle no utiliza sus índices con la paginación de EclipseLink.

He extraído el SQL enviado a la base de datos y pude reproducir el problema usando una herramienta de base de datos (DataGrip).

Ejemplo:

-- #1: sin paginación
SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
FROM <table>
WHERE colN > to_timestamp('2021-12-08', 'yyyy-mm-dd'))
ORDER BY col1 DESC;

El plan de explicación muestra que se utiliza el índice en colN. Bien.

Cuando se ejecuta la misma consulta con paginación, la consulta original se envuelve en dos subconsultas:

-- #2 con paginación de EclipseLink
SELECT * FROM (
    SELECT a.*, ROWNUM rnum  FROM (
        SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
        FROM </table><table>
        WHERE colN > to_timestamp('2021-12-08', 'yyyy-mm-dd'))
        ORDER BY col1 DESC<br>
    ) a WHERE ROWNUM <= 100
) WHERE rnum > 0;

Para esta consulta, el plan de explicación muestra que el índice en colN no se utiliza. Como resultado, consultar una tabla con millones de filas tarda de 50 a 90 segundos (dependiendo del hardware).
Nota: en mi base de datos de prueba, esta consulta devuelve 0 registros ya que los valores de colN son anteriores al 2021-12-08.

Oracle 12c introdujo la sintaxis OFFSET/FETCH:

-- #3
SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
FROM </table><table>
WHERE colN > to_timestamp('2021-12-08', 'yyyy-mm-dd'))
ORDER BY col1 DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

Usando esta sintaxis, los índices se utilizan al menos a veces como se espera. Cuando se utilizan, el tiempo de ejecución es inferior a 1 segundo, lo cual es aceptable.
Sin embargo, no pude averiguar cómo convencer a EclipseLink para que use esta sintaxis.

Si se elimina ORDER BY col1 DESC de la consulta paginada original (#2), se utiliza el índice y la consulta devuelve suficientemente rápido. Sin embargo, no devolverá los registros deseados, por lo que eso no ayuda.

¿Cómo puedo implementar consultas paginadas eficientes utilizando EclipseLink y Oracle 12?
¿Cómo puedo forzar a Oracle a utilizar el índice en colN cuando se utiliza paginación y order by?

Tags:  ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para implementar consultas paginadas de alto rendimiento utilizando EclipseLink y Oracle 12, puedes utilizar la sintaxis OFFSET/FETCH como se muestra en la consulta #3. Sin embargo, como mencionaste, EclipseLink puede no usar esta sintaxis automáticamente, por lo que es posible que debas especificarla manualmente en tu código. Puedes hacer esto utilizando una consulta nativa o configurando las pistas de consulta en tu código de EclipseLink.

    Para forzar a Oracle a utilizar el índice en colN cuando se utiliza paginación y ORDER BY, puedes intentar agregar una pista a la consulta. Puedes hacer esto agregando la pista USE_INDEX a la consulta y especificando el nombre del índice. Por ejemplo:

    SELECT /*+ USE_INDEX(<table> <index_name>) */ col1 AS a1, col2 AS a2, col3 AS a3, ...
FROM <table>
WHERE colN > to_timestamp('2021-12-08', 'yyyy-mm-dd'))
ORDER BY col1 DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

    Reemplaza </table><table> y <index_name> con el nombre de tu tabla e índice, respectivamente. Esto debería forzar a Oracle a utilizar el índice especificado para la consulta.

    Ten en cuenta que agregar pistas a una consulta puede ser complicado y puede afectar el rendimiento de la consulta de maneras inesperadas. Úsalo con precaución y asegúrate de probarlo a fondo antes de usarlo en producción.

Comments are closed.