plan - optimizar inner join sql server

Optimizar una consulta SELECT que se ejecuta lentamente en Oracle, que se ejecuta rápidamente en SQL Server

Question

Estoy tratando de ejecutar la siguiente instrucción SQL en Oracle, y lleva años ejecutarla:

SELECT orderID FROM tasks WHERE orderID NOT IN 
(SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL)

Si ejecuto solo la subparte que está en la cláusula IN, eso se ejecuta muy rápido en Oracle, es decir,

SELECT DISTINCT orderID FROM tasks WHERE
engineer1 IS NOT NULL AND engineer2 IS NOT NULL

¿Por qué toda la declaración lleva tanto tiempo en Oracle? En SQL Server toda la instrucción se ejecuta rápidamente.

¿Alternativamente hay una declaración de SQL más simple / diferente / mejor que debo usar?

Algunos detalles más sobre el problema:

• Cada orden está compuesta de muchas tareas
• Se asignará cada orden (una o más de sus tareas tendrán un conjunto de ingeniería1 e ingeniero2) o la orden puede ser sin asignar (todas sus tareas tienen valores nulos para los campos de ingeniero)
• Estoy tratando de encontrar todos los ID de pedido que no están asignados.

En caso de que haga alguna diferencia, hay ~ 120k filas en la tabla, y 3 tareas por orden, por lo que ~ 40k pedidos diferentes.

Respuestas a las respuestas:

• Preferiría una declaración de SQL que funcione tanto en SQL Server como en Oracle.
• Las tareas solo tienen un índice en orderID e taskID.
• Probé la versión NO EXISTE de la declaración, pero se ejecutó durante más de 3 minutos antes de que la cancelara. Tal vez necesita una versión JOIN de la declaración?
• También hay una tabla de "pedidos" con la columna orderID. Pero estaba tratando de simplificar la pregunta al no incluirla en la declaración SQL original.

Supongo que en la declaración SQL original, la subconsulta se ejecuta cada vez para cada fila en la primera parte de la declaración SQL, a pesar de que es estática y solo debería ejecutarse una vez.

Ejecutando

ANALYZE TABLE tasks COMPUTE STATISTICS;

hizo que mi instrucción SQL original se ejecutara mucho más rápido.

Aunque todavía tengo curiosidad por saber por qué tengo que hacer esto, y ¿cuándo debería volver a ejecutarlo?

Las estadísticas dan a Oracle la información del optimizador basado en costos que necesita para determinar la eficiencia de diferentes planes de ejecución: por ejemplo, el número de filas en una tabla, el ancho promedio de las filas, los valores más altos y más bajos por columna, el número de valores distintos por columna , factor de agrupamiento de índices, etc.

En una base de datos pequeña, puede configurar un trabajo para reunir estadísticas todas las noches y dejarlo en paz. De hecho, este es el valor predeterminado en 10g. Para implementaciones más grandes, por lo general, debe sopesar la estabilidad de los planes de ejecución frente a la forma en que los datos cambian, lo cual es un equilibrio difícil.

Oracle también tiene una función llamada "muestreo dinámico" que se utiliza para muestrear tablas para determinar estadísticas relevantes en el momento de la ejecución. Se usa mucho más a menudo con almacenes de datos donde la sobrecarga del muestreo es mayor que el aumento del rendimiento potencial para una consulta de larga ejecución.

Answer 1

"Aunque todavía tengo curiosidad por saber por qué tengo que hacer esto, y si / cuándo tendría que volver a ejecutarlo".

Las estadísticas dan a Oracle la información del optimizador basado en costos que necesita para determinar la eficiencia de diferentes planes de ejecución: por ejemplo, el número de filas en una tabla, el ancho promedio de las filas, los valores más altos y más bajos por columna, el número de valores distintos por columna , factor de agrupamiento de índices, etc.

En una base de datos pequeña, puede configurar un trabajo para reunir estadísticas todas las noches y dejarlo en paz. De hecho, este es el valor predeterminado en 10g. Para implementaciones más grandes, por lo general, debe sopesar la estabilidad de los planes de ejecución frente a la forma en que los datos cambian, lo cual es un equilibrio difícil.

Oracle también tiene una función llamada "muestreo dinámico" que se utiliza para muestrear tablas para determinar estadísticas relevantes en el momento de la ejecución. Se usa mucho más a menudo con almacenes de datos donde la sobrecarga del muestreo es mayor que el aumento del rendimiento potencial para una consulta de larga ejecución.

Answer 2

¿No es tu consulta lo mismo que

SELECT orderID FROM tasks
WHERE engineer1 IS NOT NULL OR engineer2 IS NOT NULL

?

Answer 3

¿Qué proporción de las filas en la tabla cumple la condición "ingeniero1 NO ES NULO Y el ingeniero2 NO ES NULO"?

Esto le indica (aproximadamente) si valdría la pena tratar de usar un índice para recuperar los orderid''s asociados.

Otra forma de escribir la consulta en Oracle que manejaría muy bien los casos no indexados sería:

select distinct orderid
from
(
select orderid,
       max(case when engineer1 is null and engineer2 is null then 0 else 1)
          over (partition by orderid)
          as max_null_finder
from   tasks
)
where max_null_finder = 0

Answer 4

A menudo este tipo de problema desaparece si analiza las tablas involucradas (para que Oracle tenga una mejor idea de la distribución de los datos)

ANALYZE TABLE tasks COMPUTE STATISTICS;

Answer 5

Algunas preguntas:

• ¿Cuántas filas hay en las tareas?
• ¿Qué índices están definidos en él?
• ¿La tabla ha sido analizada recientemente?

Otra forma de escribir la misma consulta sería:

select orderid from tasks
minus
select orderid from tasks
where engineer1 IS NOT NULL AND engineer2 IS NOT NULL

Sin embargo, prefiero esperar que la consulta incluya una tabla de "pedidos":

select orderid from ORDERS
minus
select orderid from tasks
where engineer1 IS NOT NULL AND engineer2 IS NOT NULL

o

select orderid from ORDERS
where orderid not in
( select orderid from tasks
  where engineer1 IS NOT NULL AND engineer2 IS NOT NULL
)

o

select orderid from ORDERS
where not exists
( select null from tasks
  where tasks.orderid = orders.orderid
  and   engineer1 IS NOT NULL OR engineer2 IS NOT NULL
)

Answer 6

Creo que muchas personas tienen el SQL correcto, pero les falta una combinación entre las consultas internas y externas.
Prueba esto:

SELECT t1.orderID 
FROM   tasks t1
WHERE  NOT EXISTS
       (SELECT 1 
        FROM   tasks t2 
        WHERE  t2.orderID   = t1.orderID
        AND    t2.engineer1 IS NOT NULL 
        AND    t2.engineer2 IS NOT NULL)

Answer 7

El optimizador de Oracle hace un buen trabajo al procesar las declaraciones MENOS. Si vuelve a escribir su consulta usando MINUS, es probable que se ejecute con bastante rapidez:

SELECT orderID FROM tasks
MINUS
SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL

Answer 8

Estoy de acuerdo con TZQTZIO, no recibo tu consulta.

Si suponemos que la consulta tiene sentido, entonces puede intentar usar EXISTS ya que algunos sugieren y evitan IN. IN no siempre es malo y hay casos probables que uno podría demostrar que en realidad funciona mejor que EXISTS.

El título de la pregunta no es muy útil. Podría establecer esta consulta en una base de datos Oracle y hacerla funcionar lentamente y hacerla funcionar rápidamente en otra. Hay muchos factores que determinan cómo la base de datos resuelve la consulta, las estadísticas de objetos, las estadísticas de esquema SYS y los parámetros, así como el rendimiento del servidor. Sqlserver vs. Oracle no es el problema aquí.

Para aquellos interesados ​​en el ajuste y el rendimiento de las consultas, y desean obtener más información, algunos de los términos de búsqueda de google son "roble tabla oráculo" y "oráculo jonathan lewis".

Answer 9

La cláusula "IN" es conocida en Oracle por ser bastante lenta. De hecho, el optimizador de consultas internas en Oracle no puede manejar las declaraciones con "IN" bastante bien. Intenta usar "EXISTS":

SELECT orderID FROM tasks WHERE orderID NOT EXISTS 
    (SELECT DISTINCT orderID FROM tasks WHERE
         engineer1 IS NOT NULL AND engineer2 IS NOT NULL)`print("code sample");`

Precaución: compruebe si la consulta genera los mismos resultados de datos.

Edith dice: ooops, la consulta no está bien formada, pero la idea general es correcta. Oracle tiene que realizar un escaneo de tabla completo para la segunda consulta (interna), generar los resultados y luego compararlos con la primera consulta (externa), por eso se está desacelerando. Tratar

SELECT orderID AS oid FROM tasks WHERE NOT EXISTS 
    (SELECT DISTINCT orderID AS oid2 FROM tasks WHERE
         engineer1 IS NOT NULL AND engineer2 IS NOT NULL and oid=oid2)

o algo similar ;-)

Answer 10

Las sub-consultas son "malas" con Oracle. En general, es mejor usar combinaciones.

Aquí hay un artículo sobre cómo reescribir sus subconsultas con join: http://www.dba-oracle.com/sql/t_rewrite_subqueries_performance.htm

Answer 11

Nueva toma.

Iff :

• La función COUNT () no cuenta los valores NULL

y

• Desea el orderID de todas las tareas donde ninguna de las tareas tiene ni engineer1 ni engineer2 configuradas en un valor

entonces esto debería hacer lo que quieras:

SELECT orderID
FROM tasks
GROUP BY orderID
HAVING COUNT(engineer1) = 0 AND COUNT(engineer2) = 0

Por favor pruébalo.

Answer 12

Otra opción es usar MINUS (EXCEPTO en MSSQL)

SELECT orderID FROM tasks
MINUS
SELECT DISTINCT orderID FROM tasks WHERE engineer1 IS NOT NULL 
AND engineer2 IS NOT NULL

Answer 13

Qué tal si :

SELECT DISTINCT orderID FROM tasks t1 WHERE NOT EXISTS (SELECT * FROM tasks t2 WHERE t2.orderID=t1.orderID AND (engineer1 IS NOT NULL OR engineer2 IS NOT NULL));

No soy un gurú de la optimización, pero tal vez también pasó por alto algunos índices en su base de datos Oracle.

Answer 14

Si decide crear una tabla ORDERS, agregaría un indicador ALLOCATED y crearía un índice de mapa de bits. Este enfoque también lo obliga a modificar la lógica comercial para mantener el indicador actualizado, pero las consultas serán muy rápidas. Depende de cuán críticas sean las consultas para la aplicación.

En cuanto a las respuestas, cuanto más simple mejor en este caso. Olvídese de subconsultas, uniones, bys separados y grupales, ¡no son necesarios en absoluto!

Answer 15

Si no tiene ningún índice sobre las columnas Engineer1 e Engineer2, siempre generará una exploración de tabla en SQL Server y su equivalente en Oracle.

Si solo necesita los pedidos que tienen tareas sin asignar, entonces lo siguiente debería funcionar bien en ambas plataformas, pero también debería considerar agregar los índices a la tabla Tareas para mejorar el rendimiento de la consulta.

SELECT DISTINCT orderID 
FROM tasks 
WHERE (engineer1 IS NULL OR engineer2 IS NULL)

Answer 16

Yo trataría de usar uniones en su lugar

SELECT 
    t.orderID 
FROM 
    tasks  t
    LEFT JOIN tasks t1
        ON t.orderID =  t1.orderID
        AND t1.engineer1 IS NOT NULL 
        AND t1.engineer2 IS NOT NULL
WHERE
    t1.orderID IS NULL 

también su consulta original probablemente sería más fácil de entender si se especificara como:

SELECT orderID FROM orders WHERE orderID NOT IN 
(SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL)

(suponiendo que tiene tabla de órdenes con todas las órdenes enumeradas)

que luego se puede reescribir usando uniones como:

SELECT 
    o.orderID 
FROM 
    orders o
    LEFT JOIN tasks t
        ON o.orderID =  t.orderID
        AND t.engineer1 IS NOT NULL 
        AND t.engineer2 IS NOT NULL
WHERE
    t.orderID IS NULL 

Answer 17

Aquí hay un enfoque alternativo que creo que da lo que quiere:

SELECT orderID
 FROM tasks
 GROUP BY orderID
 HAVING COUNT(engineer1) = 0 OR COUNT(engineer2) = 0

No estoy seguro de si quiere "Y" o "O" en la cláusula HAVING. Parece que de acuerdo con la lógica comercial, estos dos campos deberían estar poblados o ambos ser NULL; si esto está garantizado, entonces puede reducir la condición a simplemente verificar engineer1.

Tu consulta original, creo, daría varias filas por orderID, mientras que la mía solo dará una. Supongo que esto está bien, ya que solo está obteniendo el ID de pedido.

Answer 18

Estoy de acuerdo con ΤΖΩΤΖΙΟΥ y wearejimbo que tu consulta debería ser ...

SELECT DISTINCT orderID FROM Tasks 
WHERE Engineer1 IS NULL OR Engineer2 IS NULL;

No sé sobre SQL Server, pero esta consulta no podrá aprovechar ningún índice porque las filas nulas no están en índices. La solución a esto sería volver a escribir la consulta de una manera que permita crear un índice basado en función que solo incluya las filas de valores nulos. Esto podría hacerse con NVL2, pero probablemente no sería portátil para SQL Server.

Creo que la mejor respuesta no es la que cumple con sus criterios y que es escribir una declaración diferente para cada plataforma que sea mejor para esa plataforma.