Vamos a realizar un pequeño análisis para ver si hay algún punto mejorable en la siguiente consulta del cliente. Es una consulta que se suele consultar a nivel anual, pero como se dispara de tiempos consultando un año vamos a consultarla para un solo día.
Lo primero que vamos hacer es generar el tkprof. En el tkprof vamos a ver la carga de cada paso del row source “Row Source Generator” y vamos a poder determinar qué está sucediendo internamente en el tiempo de ejecución de este select.
Ahí vemos un posible problema, la consulta internamente realiza un producto cartesiano ¿pero es realmente este el problema? Si miramos en detalle el tiempo de ese MERGE JOIN CARTESIAN es de 6 milisegundos, ¡muy poco tiempo!
Por mucho que asuste un producto cartesiano, tenemos que ver esta información a nivel global. Si vemos, se están combinando 177 filas de la tabla T_TEMP_PARAM con 1 fila, es decir, para Oracle una combinación de 177 filas con 1, la forma más rápida para acceder y recorrer esas filas, es vía producto cartesiano, no hay necesidad de forzar un nested loop o un hash join. Es como si añadiésemos una columna más al conjunto de resultados.
Entonces, ¿dónde se está produciendo grueso de la ejecución? Aquí:
Estas tres operaciones del row source, son realmente las que más consumen, con más de 100.000 bloques leídos en memoria. Otro punto más que nos llama la atención de este cruce es el tiempo, pasamos de 7 milisegundos a 1 segundo.
Si miramos el árbol de ejecución, vemos que ese salto tanto en bloques leídos como en tiempo de ejecución se produce al acceder a la tabla particionada
En este punto, es donde deberíamos atacar para optimizar la consulta.
El acceso a esta tabla particionada se encuentra en la consulta principal, ahí es donde consultamos dicha tabla. Al utilizar el “exists”, estamos haciendo que se accede varias veces a esa tabla en función de los elementos que existan. Entonces, ¿sería correcto usar “exists” o habría que cambiar a “in”?
Hay que tener claro una cosa, cuando nosotros tenemos una select que tiene una subconsulta como es nuestro caso, si es una subconsulta «in” y tenemos que el grueso de la ejecución se lo lleva la select y la subconsulta “in”, tiene poco peso, es decir, que es una consulta ligera, vamos a tener mejor rendimiento utilizando “in” que utilizando “exists”, porque “exists” me va dar un mejor beneficio cuando el grueso de “exists” sea muy grande respecto a una ejecución ligera de la consulta principal.
En este caso, ambas subconsultas son ejecuciones ligeras respecto a la principal con lo que en este caso es mejor usar “in”.
Modificamos esa sentencia cambiando “exists” por “in y ahora la consulta vuela, de un tiempo de CPU de 1,45 a 0.1, prácticamente inmediato.
Además, ¡ha desaparecido el producto cartesiano!