sql - rapidas - Mejore el rendimiento de consultas oráculo sin indexar

optimizar inner join sql server

Question

¿Cuáles son algunas cosas que puedo hacer para mejorar el rendimiento de consultas de una consulta oráculo sin crear índices?

Aquí está la consulta que intento ejecutar más rápido:

SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM items a,
itempages b,
keygroupdata c
WHERE a.ItemType IN (112,115,189,241)
AND a.ItemNum = b.ItemNum
AND b.ItemNum = c.ItemNum
ORDER BY a.DateStored DESC

Ninguna de estas columnas está indexada y cada una de las tablas contiene millones de registros. No hace falta decir que lleva más de 3 minutos y medio ejecutar la consulta. Esta es una base de datos de terceros en un entorno de producción y no tengo permiso para crear índices, por lo que las mejoras de rendimiento deberían realizarse en la consulta misma.

¡Gracias!

Answer 1

¿Es esta una consulta que ejecutas a menudo? Parece que sería del interés del propietario del DB crear los índices que necesita para acelerar esta consulta. ¡Los 3.5 minutos que gasta ejecutar la consulta deben tener algún impacto en su entorno de producción!

Además, ¿han estado ejecutando estadísticas de actualización en las tablas? Eso podría mejorar el rendimiento, ya que la orden de unión se calcula en función de las estadísticas de las tablas.

Por cierto, ¿qué se te permite hacer? ¿Acabo de leer? Si puede crear tablas temporales y poner índices sobre ellas, podría considerar hacer copias temporales de la tabla, indexarlas y luego hacer la unión asistida por índice con las copias temporales.

Answer 2

A veces puede ver un beneficio agregando vías adicionales para que el optimizador elija agregando lo que parecen ser elementos redundantes a la cláusula where.

Por ejemplo, tiene A.ItemNum = B.ItemNum AND B.ItemNum = C.ItemNum. Intenta agregar A.ItemNum = C.ItemNum también. Sin embargo, estoy bastante seguro de que el optimizador es lo suficientemente inteligente como para darse cuenta por sí mismo, vale la pena intentarlo.

Answer 3

Eliminar la orden por

realice la clasificación, después de tirar de las filas a su aplicación.

Answer 4

Pídale a un tercero que indexe sus columnas de unión, ¡como deberían haber hecho en primer lugar! Sin índices, Oracle no tiene nada más que la fuerza bruta.

Answer 5

Primero reescribo la consulta para que sea el estándar ANSI:

SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM items a
INNER JOIN itempages b ON b.ItemNum = a.ItemNum
INNER JOIN keygroupdata c ON c.ItemNum = b.ItemNum
WHERE a.ItemType IN (112,115,189,241)
ORDER BY a.DateStored DESC

Esto facilita la lectura y comprensión de lo que está sucediendo. También ayuda a no cometer errores (es decir, Cross Joining) que pueden causar problemas realmente grandes. Luego obtendría el plan Explain para ver qué está haciendo el DBMS con esa consulta. ¿Está tratando de usar algunos índices? ¿Está uniendo las mesas correctamente?

Luego revisaría las tablas con las que estoy trabajando para ver si ya existen índices que pudiera utilizar para agilizar mi consulta. Finalmente, como todos los demás sugirieron que eliminaría la cláusula Order By y simplemente lo haría en el código.

Answer 6

Primero, mira el plan de ejecución. ¿Refleja con precisión el número de filas que se recuperarán en cada etapa de la ejecución de la consulta? ¿Qué tan selectivo es el predicado "a.ItemType IN (112,115,189,241)"? ¿El plan de ejecución muestra algún uso de espacio de disco temporal para uniones o géneros?

En realidad, tal vez pueda modificar la pregunta para incluir el plan de ejecución.

También asegúrese de que no tenga deshabilitadas las combinaciones de hash, que a veces es el caso en los sistemas sintonizados con OLTP, ya que son la forma más eficiente de equiparar los datos masivos en Oracle. Deberían aparecer en el plan de ejecución.

Answer 7

Puede intentar crear una vista materializada en cualquiera de esas tablas. A continuación, puede crear un índice en la vista materializada que ayudará a acelerar la consulta (que luego consultaría la vista materializada en lugar de la tabla sin formato).

Por supuesto, si su tabla subyacente se actualiza, su vista e índices deberán actualizarse.

Answer 8

Puede intentar filtrar en el tipo de elemento antes de unir sus tablas, como se muestra aquí.

Si está ejecutando en Oracle antes de 9i, esto a veces daría sorprendentes beneficios.

select 
  c.claimnumber,
  a.itemdate, 
  c.dtn,
  b.filepath
from 
  (
  select itemdate
  from items it
  where it.itemtype in(112,115,189,241)
  ) a
  itempages b,
  keygroupdata c
where a.itemnum = b.itemnum
  and b.itemnum = c.itemnum

También puede intentar agregar las sugerencias / + REGLA / o / + ORDENADO / para ver qué ocurre ... una vez más, particularmente con versiones anteriores, estas a veces darían resultados sorprendentes.

SELECT /*+RULE*/
  c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM
  items a,
  itempages b,
  keygroupdata c
WHERE a.ItemType IN (112,115,189,241)
  AND a.ItemNum = b.ItemNum
  AND b.ItemNum = c.ItemNum
ORDER BY a.DateStored DESC

Answer 9

Si las entradas de consulta son constantes o predecibles (el itemType IN (...) ), una alternativa sería ejecutar la consulta una o dos veces al día y almacenar los resultados en una tabla local, con índices donde corresponda.

A continuación, puede hacer que la costosa consulta esté ''fuera de línea'' y tenga resultados más rápidos / mejores para una consulta interactiva.

Answer 10

Sin indexación, esa consulta solo empeorará a medida que aumente el tamaño de la tabla. Dicho esto, intente eliminar la orden por cláusula y hacer ese tipo en el lado del cliente.

Answer 11

¿Las estadísticas se reúnen en estas tablas? De lo contrario, reunir estadísticas podría cambiar el plan de ejecución, aunque no necesariamente sería para mejor.

Aparte de eso, mira el plan de ejecución. Puede ver que está uniendo las tablas en un orden no óptimo (por ejemplo, podría estar uniendo byc antes de unirse con un que tiene la condición de filtro).

Puede utilizar sugerencias para intentar afectar las rutas de acceso, el orden de unión o el método de unión.

Actualización : Responder al comentario me llevó a esta presentación, que podría ser útil o al menos interesante.

Answer 12

Dependiendo del tipo de datos de la columna ItemType, puede experimentar una ejecución más rápida usando lo siguiente si es un varchar, Oracle hará conversiones implícitas.

SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM items a,
itempages b,
keygroupdata c
WHERE ((a.ItemType IN (''112'',''115'',''189'',''241''))
AND (a.ItemNum = b.ItemNum)
AND (b.ItemNum = c.ItemNum))
ORDER BY a.DateStored DESC

Answer 13

Si dice que no hay índices, ¿significa esto que no hay claves primarias o externas definidas? Obviamente, el análisis de las tablas y la recopilación de estadísticas son importantes, pero si no existen los metadatos, como definir cómo se unen las tablas, Oracle puede elegir una ruta de ejecución deficiente.

En ese caso, usar una pista como / * + ORDERED * / bien puede ser la única opción para que el optimizador elija de manera confiable una buena ruta de ejecución. También puede valer la pena agregar claves externas y claves principales, pero definirlas como DESHABILITAR y VALIDAR.

Supongo que la utilidad de este comentario depende de cuán lejos va la aversión a los índices para el YMMV.

Answer 14

Sé que este hilo es muy viejo, pero para los motores de búsqueda aún quería ofrecer una solución alternativa que funcione en Oracle y que según los datos podría ser mucho más rápida.

with a as (
  select 
    * 
  from 
    items 
  where 
    ItemType IN (112,115,189,241)
)
SELECT 
  c.ClaimNumber
  , a.ItemDate
  , c.DTN, b.FilePath
FROM 
  a,
  itempages b,
  keygroupdata c
WHERE 
  a.ItemNum = b.ItemNum
  AND b.ItemNum = c.ItemNum
ORDER BY 
  a.DateStored DESC

También puede probar el /*+ MATERIALIZE */ hint en la cláusula WITH .

De hecho, encuentro la sintaxis de unión antigua de Oracle mucho más fácil de leer que ansi sql ^^

Answer 15

Primero cree una vista en esta consulta y luego genere una tabla desde esta vista. Cree también un índice en la fecha, haga un trabajo y programe en la medianoche cuando el sistema esté inactivo.

Answer 16

Bueno, como no puedes crear índices, me aseguraré de que las estadísticas estén todas actualizadas, reescribiré la consulta de esta manera:

with a as (select /*+ MATERIALIZE */ ItemType, ItemNum, DateStored, ItemDate from items where ItemType in (112,115,189,241)) SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath FROM a, itempages b, keygroupdata c WHERE a.ItemNum = b.ItemNum AND b.ItemNum = c.ItemNum ORDER BY a.DateStored DESC