Para consultar filas top-n en Oracle, es general utilizar ROWNUM. Entonces, la siguiente consulta parece estar bien (obtiene los 5 pagos más recientes):

select a.paydate, a.amount
from (
  select t.paydate, t.amount
  from payments t
  where t.some_id = id
  order by t.paydate desc
) a
where rownum <= 5;

Pero para mesas muy grandes, es ineficiente; para mí, se ejecuta durante ~ 10 minutos. Así que probé otras consultas y terminé con esta que se ejecuta durante menos de un segundo:

select *
from (
  select  a.*, rownum
  from (select t.paydate, t.amount
        from payments t
        where t.some_id = id
        order by t.paydate desc) a
)
where rownum <= 5;

Para saber qué está sucediendo, busqué planes de ejecución para cada consulta. Para la primera consulta:

SELECT STATEMENT, GOAL = ALL_ROWS   7   5   175
COUNT STOPKEY           
VIEW    7   5   175
TABLE ACCESS BY INDEX ROWID 7   316576866   6331537320
INDEX FULL SCAN DESCENDING  4   6   

Y por segundo:

SELECT STATEMENT, GOAL = ALL_ROWS   86  5   175
COUNT STOPKEY           
VIEW    86  81  2835
COUNT           
VIEW    86  81  1782
SORT ORDER BY   86  81  1620
TABLE ACCESS BY INDEX ROWID 85  81  1620
INDEX RANGE SCAN    4   81  

Obviamente, es INDEX FULL SCAN DESCENDING lo que hace que la primera consulta sea ineficaz para tablas grandes. Pero realmente no puedo diferenciar la lógica de dos consultas mirándolas. ¿Alguien podría explicarme las diferencias lógicas entre dos consultas en lenguaje humano?

¡Gracias de antemano!

8
Bazi 3 feb. 2012 a las 15:30
2
Id es una variable de vinculación, no (debería ser: id?) Si es así, ¿qué valor se usa (¿el mismo?)
 – 
tbone
3 feb. 2012 a las 16:30
2
No creo que el rownum que estás usando para el filtro en la segunda versión sea el mismo que en la primera versión; ¿cree que necesita asignar un alias a su segunda consulta y hacer referencia a eso, o agregar order by rownum en la consulta contra a? Sin embargo, dudo que esto esté afectando la velocidad.
 – 
Alex Poole
3 feb. 2012 a las 17:12

1 respuesta

La mejor respuesta

En primer lugar, como se menciona en el comentario de Alex, no estoy seguro de que tu segunda versión esté 100% garantizada para darte las filas correctas, ya que el bloque "medio" de la consulta no tiene un order by explícito. , Oracle no tiene la obligación de pasar las filas al bloque de consulta externo en ningún orden específico. Sin embargo, no parece haber ninguna razón en particular por la que cambiaría el orden en el que se pasan las filas desde el bloque más interno, por lo que en la práctica probablemente funcionará.

Y esta es la razón por la que Oracle elige un plan diferente para la segunda consulta; lógicamente, no puede aplicar la operación STOPKEY al bloque de consulta más interno.

Creo que en el primer caso, el optimizador asume que los valores de id están bien distribuidos y, para cualquier valor dado, es probable que haya algunas transacciones muy recientes. Como puede ver que solo necesita encontrar las 5 coincidencias más recientes, calcula que parece ser más eficiente escanear las filas en orden descendente de paydate usando un índice, buscar la identificación correspondiente y otros datos de la mesa y deténgase cuando encuentre las primeras 5 coincidencias. Sospecho que vería un rendimiento muy diferente para esta consulta dependiendo del valor de identificación específico que use; si la identificación tiene mucha actividad reciente, las filas se deben encontrar muy rápidamente, pero si no es así, el escaneo de índice puede que tenga que hacer mucho más trabajo.

En el segundo caso, creo que no se puede aplicar la optimización STOPKEY al bloque más interno debido a la capa adicional de anidación. En ese caso, el escaneo completo del índice se volvería mucho menos atractivo, ya que siempre necesitaría escanear todo el índice. Por lo tanto, elige hacer una búsqueda de índice el id (supongo) seguida de una clasificación real en la fecha. Si el valor de id dado coincide con un pequeño subconjunto de filas, es probable que esto sea más eficiente, pero si da un id que tiene muchas filas repartidas por toda la tabla, lo esperaría para volverse más lento, ya que tendrá que acceder y ordenar muchas filas.

Entonces, supongo que sus pruebas han usado id valor (s) que tienen relativamente pocas filas que no son muy recientes. Si este fuera un caso de uso típico, entonces la segunda consulta probablemente sea mejor para usted (nuevamente, con la advertencia de que no estoy seguro de que esté técnicamente garantizado para producir el conjunto de resultados correcto). Pero si es más probable que los valores típicos tengan muchas filas coincidentes y / o sea más probable que tengan 5 filas muy recientes, entonces la primera consulta y el plan podrían ser mejores.

3
Dave Costa 3 feb. 2012 a las 20:32
¡Gran explicación! Gracias. @Alex: parece que es mejor agregar order by rownum ya que agrega "SORT ORDER BY STOPRKEY" en el plan ejecutivo, mientras que el alias rownum elimina "COUNT STOPKEY" en el plan ejecutivo. Pero, como notó, no he visto cambios en la velocidad.
 – 
Bazi
4 feb. 2012 a las 09:21