Tips para optimizar sus consultas PL/SQL
Cuando se empieza a realizar consultas SQL no sólo es escribir, sino escribir para que funcione de manera eficiente y rápida. El rendimiento de las consultas SQL es fundamental para cualquier aplicación que se construya utilizando Oracle.
La siguiente es una lista de algunos tips que se pueden utilizar como guía para escribir y revisar consultas SQL. Esto no es una lista exhaustiva para obtener los mejores resultados de performance, pero puede servir como una referencia rápida para evitar las cosas comunes con Oracle PLSQL:
- La mejor manera de optimizar las sentencias SQL es creando los índices, Los Índices permiten un acceso más rápido a la base de datos permitiendo un rápido acceso más rápido a los registros de la tabla.
- Cuando sea posible evite el uso del LIKE
- No utilice el operador set UNION si el objetivo se puede lograr a través de UNION ALL. UNION incurre en una operación de ordenación extra que se puede evitar.
- Seleccione SÓLO aquellas columnas de una consulta que sean necesarias. Las columnas adicionales que no se utilizan realmente, incurren en más E/S en la base de datos y aumentan el tráfico de red.
- No utilice la palabra clave DISTINCTsi el objetivo se puede lograr de otra manera. DISTINCT incurre en una operación de ordenación extras, por lo tanto, ralentiza sus consultas.
- Cuando se utilizan varias tablas, consulte siempre una columna utilizando un alias o utilizando el nombre completo. No deje el trabajo de conjetura para Oracle.
- Utilizar alias significativos para tablas/vistas
- Al escribir sub-consultas, haga uso del operador EXISTS siempre que sea posible, ya que Oracle sabe que una vez que se ha encontrado una coincidencia, puede detenerse y evitar una exploración de tabla completa.
- Si el predicado selectivo está en la sub consulta, use IN.
- Si el predicado selectivo está en la consulta padre, utilice EXISTS.
- No modifique las columnas indexadas con funciones como RTRIM, TO_CHAR, UPPER, TRUNC ya que esto evitará que el optimizador identifique el índice. Si es posible, realice la modificación de la condición. Si accede a la columna indexada mediante una función (por ejemplo, NVL), considere la posibilidad de crear un índice basado en función.
- Evite los siguientes tipos de expresiones complejas: NVL (columna, -999) = …. TO_DATE (), TO_NUMBER (), y así sucesivamente
- Estas expresiones impiden que el optimizador asigne estimaciones de cardinalidad o selectividad válidas y, a su vez, puede afectar al plan general y al método de combinación
- Siempre es mejor escribir instrucciones SQL separadas para diferentes tareas, pero si debe usar una sentencia SQL, puede hacer una declaración muy compleja un poco menos compleja utilizando el operador UNION ALL
- No se recomiendan combinaciones a vistas complejas, en particular las combinaciones de una vista compleja a otra esto hace que la vista completa se instancie, y la consulta se ejecuta contra los datos de vista
- La consulta desde una vista requiere que todas las tablas de la vista a la que se acceda para que devuelvan los datos. Si no es necesario, entonces no utilice la vista. En su lugar, utilice la tablas, solo si es necesario, defina una nueva vista.
- Mientras consulta en una tabla particionada intente utilizar la clave de partición en la cláusula «WHERE» si es posible.
- Evite hacer una ORDER BY en un gran conjunto de datos, especialmente si el tiempo de respuesta es importante.
- Considere cambiar el OPTIMIZER MODE a FIRST_ROWS (n) si el tiempo de respuesta es importante. El valor predeterminado es ALL_ROWS, lo que proporciona un mejor rendimiento.
- Utilice instrucciones CASE en lugar de DECODE (especialmente donde están involucrados DECODEs anidados) porque aumentan la legibilidad de la consulta inmensamente.
- No utilice HINTS a menos que el rendimiento este bien.
- Compruebe si las estadísticas de los objetos utilizados en la consulta están actualizadas. Si no, use el paquete DBMS_STATS para recopilar el mismo.
- Siempre es bueno entender los datos tanto funcionalmente como su diversidad y volumen para afinar la consulta. El uso de estadísticas e histogramas puede conducir la consulta hacia un plan mejor.
- Si la consulta requiere una respuesta rápida en lugar de un buen rendimiento, tratar de evitar tipos (group by, order by, etc.). Para obtener un buen rendimiento, el modo de optimizador debe establecerse en ALL ROWS.
- SQL Tuning Advisor y SQL Access Advisor pueden utilizarse para asesorar al sistema sobre la adaptación de SQL específicos su combinación y vías de acceso, sin embargo, el asesoramiento generado por estas herramientas puede no ser siempre aplicable.
Ahora es su turno si tiene más consejos que han utilizado o algo no es aplicable, añadirlos en la sección de comentarios. Su opinión es muy valiosa y sería útil para otros también.
Conclusiones
El rendimiento de las consultas SQL depende de múltiples factores, entre ellos el modelo de base de datos, los índices disponibles y el tipo de información que desea recuperar. La mejor manera de mantener un registro de lo que está pasando con sus consultas es analizar el plan de ejecución producida por el optimizador. Se puede utilizar para experimentar y encontrar una mejor solución.
Sería útil algún tip sobre cómo identificar el orden de acceso a las tablas en una consulta, cuál se debe consultar primero