query lenta

[mariomario89]

Muy buenas! tengo un problema con unas consultas que al ejecutarlas tarda bastante en mostrar el resultado . Algunas de esas columnas tienen sus indices asignados. Pruebo a hacer la consulta de tres modos diferentes pero funciona de igual lentitud.

Código SQL:

SELECT "msisdn", "status", "optin_date", "optout_date", 
 (trunc("optout_date")-trunc("optin_date")) AS "life"
FROM "subscriber"
WHERE "status" = 0
UNION ALL
SELECT "msisdn", "status", "optin_date", "optout_date", 
 (trunc(sysdate)-trunc("optin_date")) AS "life"
FROM "subscriber"
WHERE "status" = 1;
 

Código SQL:

SELECT "msisdn", "status", "optin_date", "optout_date", 
CASE "status" WHEN 0 THEN (trunc("optout_date")-trunc("optin_date"))
WHEN 1 THEN (trunc(sysdate)-trunc("optin_date"))
END AS "life"
FROM "subscriber";
 

Código SQL:

SELECT "msisdn", "status", "optin_date", "optout_date", 
decode ("status", 0 , (trunc("optout_date")-trunc("optin_date")) ,
    1 , (trunc(sysdate)-trunc("optin_date")) ) AS "life"
FROM "subscriber";
 

esta es la tabla (subscriber):



Código SQL:

Name                                                                                                                                                                          NULL?    TYPE
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 id                                                                                                                                                                               NOT NULL NUMBER(11)
 id_user                                                                                                                                                                          NOT NULL NUMBER(11)
 id_service                                                                                                                                                                       NOT NULL NUMBER(11)
 msisdn                                                                                                                                                                           NOT NULL NUMBER(20)
 nextbill                                                                                                                                                                         NOT NULL DATE
 credits                                                                                                                                                                          NOT NULL NUMBER(11)
 failed_trys                                                                                                                                                                      NOT NULL NUMBER(11)
 last_attempt                                                                                                                                                                      DATE
 STATUS                                                                                                                                                                           NOT NULL NUMBER(4)
 optin_date                                                                                                                                                                       NOT NULL DATE
 optout_date                                                                                                                                                                       DATE
 id_source                                                                                                                                                                      NOT NULL NUMBER(11)
 id_campaign                                                                                                                                                                  NOT NULL NUMBER(20)
 id_campaign_detail_promo_sms                                                                                                                                      NOT NULL NUMBER(20)
 

alguien que pueda dar algún consejo de optimizacion ??

gracias!!

[gnzsoloyo]

Algunas de esas columnas tienen sus indices asignados

Primer consejo: Si usas un campo en el WHERE, asegurate que tenga un índice que sirva.
Hay dos cosas que seguro no debes hacer en campos indexados:
1) No generes un índice sobre un campo que tenga dos valores posibles (0/1, por ejemplo). Un campo de ese tipo en el mejor de los casos repartirá el 50% de los registros en cada entrada, por lo que el analizador de consultas de Oracle simplemente lo descartará y leerá la tabla.
2) Si tienes un campo DATE indexado, y los datos son de tipo Fecha y Hora (dd/mm/aaaa HH:mm:ss), no lo trunques si lo usas como parámetro del WHERE. Cuando lo haces, como el tipo comparado (fecha corta) es diferente al tipo declarado de la clave del indice (fecha larga), Oracle descarta el índice, y lee la tabla.

En esencia tu problema de lentitud parece ser causado porque al no haber parametrizaciones útiles, o relaciones que usen los índices, realiza un full table scan, que es la peor forma de consulta que exista, después del producto cartesiano.

Por cierto, en tu primera consulta, si el campo status es un flag de dos estados posibles, entonces no tiene ningún sentido hacer un UNION, porque generaría el mismo resultado que una consulta común, con el agregado de un ORDER BY sobre ese campo.

[mariomario89]

Gracias por el aporte gnzsoloyo!

Al principio si que puso indice el status pero me di cuenta que no servia para nada ya que solo hay dos tipos de valores en la columna XD

En los campos date de momento no he añadido ningun indice ya que no sabia seguro si era necesario, el trunc lo pongo ya que si no me da un resultado con demasiados decimales y no quiero que me los muestre. ¿sabes algún modo mejor de hacer la query sin trunc pero que no muestre decimales?

saludos!!

[gnzsoloyo]

Pruébalo así:

Código SQL:

SELECT 
    msisdn, 
    STATUS, 
    optin_date, 
    optout_date, 
    TRUNC(DECODE(STATUS, 0, (optout_date-optin_date), 1, (SYSDATE-optin_date), 0))life
  FROM subscriber;
 

[jc3000]

Un pequeño comentario......

Para el campo que tiene dos valores posibles, quizás sería útil un índice BITMAP y respecto al UNION ALL, si no recuerdo mal, Oracle no te resuelve las dos querys a la vez, te resuelve primero una y luego la otra

[huesos52]

Si bien es cierto que un indice bitmap funciona bien para campos con poca repetición, también tendrías bastantes problemas si esta es una tabla transaccional.

Este tipo de indices son muy buenos para bodegas de datos, donde los cargues de datos no son muy frecuentes y las consultas son muchas. Pero si la tabla recibe o modifica registros muy seguido, esto conlleva a un recreate del indice cada que se haga un cambio en la tabla lo cual puede resultar bastante costoso.

[mariomario89]

gnzsoloyo he probado ha hacerla como me has dicho pero sigue funcionando lenta ... pero probando desd SQL la query es bastante rapida .. solo me ocurre si lo hago desde interfaz grafica ... probe ha crear un indice bitmap pero al ser mi version de oracle un standard edition no permite crear indices bitmap

[gnzsoloyo]

Bueno, por lo que dices, no es un problema de la consulta, sino de la interfaz...

¿Qué interfaz usas para hacerla?

[mariomario89]

utilizo Navicat ... es una interfaz que me sirve para hacer realizar conexiones con bbdd de oracle, mySQL, postgreSQL, etc

[huesos52]

Dices que por consola es rápida y por el navicat es lenta?
Esto es raro.
Se me ocurre que tengas un cuello de botella en el driver jdbc.
Mira si puedes actualizar el driver, depronto trabajas con una versión viejita y puede presentar algún bug.