optimizacion - ¿Por qué una combinación de SQL elige un plan de consulta subóptimo?

optimizar consultas sql oracle

Question

Ok, entonces me doy cuenta de que esta es una pregunta bastante vaga, pero tengan paciencia conmigo.

He experimentado este problema en numerosas ocasiones con consultas diferentes y no relacionadas. La consulta a continuación toma muchos minutos para ejecutarse:

SELECT <Fields>
FROM <Multiple Tables Joined>
    LEFT JOIN (SELECT <Fields> FROM <Multiple Tables Joined> ) ON <Condition>

Sin embargo, con solo agregar la sugerencia de combinación, consulta los ejecutables en solo segundos:

SELECT <Fields>
FROM <Multiple Tables Joined>
    LEFT HASH JOIN (SELECT <Fields> FROM <Multiple Tables Joined> ) ON <Condition>

Lo extraño es que el tipo de JOIN especificado en la sugerencia no es realmente lo que mejora el rendimiento. Parece ser porque la sugerencia hace que el optimizador ejecute la consulta secundaria aisladamente y luego se una. Veo la misma mejora de rendimiento si creo una función de valor de tabla (no una en línea) para la subconsulta. p.ej

SELECT <Fields>
FROM <Multiple Tables Joined>
    LEFT JOIN dbo.MySubQueryFunction() ON <Condition>

¿Alguien tiene alguna idea de por qué el optimizador es tan tonto en este caso?

Answer 1

Optimizer es un algoritmo. No es tonto o inteligente, funciona de la manera en que está programado.

Hash join implica crear una tabla hash en una fuente de fila más pequeña, por eso la consulta interna debe ejecutarse primero.

En el primer caso, el optimizador podría haber elegido un nested loop . Impulsó la condición de unión en la consulta interna y ejecutó la consulta interna en cada iteración con un predicado adicional. Es posible que no encuentre un índice apropiado para este predicado, y se realizó un full table scan en cada iteración.

Es difícil decir por qué sucede esto a menos que publique su consulta exacta y cuántas filas hay en sus tablas.

Con una función de tabla, es imposible insertar una condición de unión en la consulta interna, por eso se está ejecutando solo una vez.

Answer 2

Si cualquiera de esas tablas es una variable de tabla, el optimizador usa una mala estimación de 0 filas y generalmente elige el bucle anidado como la técnica de unión.

Lo hace debido a la falta de estadísticas sobre las tablas involucradas.

Answer 3

Dentro de SQL Server 2005: T-SQL Query responde estas y muchas otras preguntas. Uno de los mejores looks bajo la captación de datos T-SQL y el procesamiento de verbos que he visto. (No, no soy autor del libro, ni estoy afiliado a ningún autor o autores del libro, ni a Microsoft, ni a Microsoft Press. Esto es simplemente un trabajo increíble, y varios DBA han convertido esto en el pasado un par de años de acuerdo).