Consulta a tabla particionada

[eduar2083]

Buenas con tod@s,
Sucede que tengo una tabla particionada por rango, las particiones son generadas diariamente tomando como índice de partición una columna llamada S_TIMESTAMP de tipo Date y tengo la siguiente consulta:

Código SQL:

SELECT
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140618)
WHERE
    S_TIMESTAMP > TO_DATE('20140616205959', 'YYYYMMDDHH24MISS')
UNION
SELECT
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140619      )
UNION
SELECT
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140620      )
UNION
SELECT
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140621      )
UNION
SELECT
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140622)
WHERE
    S_TIMESTAMP <= TO_DATE('20140620205959', 'YYYYMMDDHH24MISS');
 

Pero al analizar el plan de ejecución me arroja varios Table Access Full.

https://www.dropbox.com/sh/2ha2zqfr8...#f:print01.png

Para el primer select me toma correctamente el índice creado (IDX_GPRS_S_TIMESTAMP) no hay Table Access Full. Para los selects del centro que no tienen where pienso que se debe porque se hace un barrido de toda la partición, sin embargo, me extraña el último select que es muy similar al primero ya que filtra por el campo S_TIMESTAMP que está indizado.

Por favor si me pudieran orientar para optimizar está consulta.

Muchas gracias, saludos.

[gnzsoloyo]

Lo mas probable es que el ultimo WHERE descarte el indice porque una condición por mayor o igual a un valor dado, tiende a tener un costo de consulta equivalente a leer la tabla entera... dado que no pueden determinar cual es el valor de la otra cota.
Dado que en tales condiciones deberán leer los dos extremos del indice, es probable que la búsqueda por método binario termine representando un 50% del total de entradas y cuando eso pasa el indice se descarta.
Eso, al menos, es lo que explican los manuales en el capitulo del calculo de costos de consulta. Yo todavia los conservo.

[eduar2083]

He agregado hints INDEX para forzar el uso del índice:

Código SQL:

SELECT
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140618)
WHERE
    S_TIMESTAMP > TO_DATE('20140616205959', 'YYYYMMDDHH24MISS')
UNION
SELECT /*+ INDEX(B IDX_GPRS_S_TIMESTAMP) */
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140619      ) B
UNION
SELECT /*+ INDEX(C IDX_GPRS_S_TIMESTAMP) */
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140620      ) C
UNION
SELECT /*+ INDEX(D IDX_GPRS_S_TIMESTAMP) */
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140621      ) D
UNION
SELECT /*+ INDEX(E IDX_GPRS_S_TIMESTAMP) */
    COL_A,
    COL_B,
    COL_C,
    S_TIMESTAMP
FROM DM.CDR_GPRS PARTITION(P_20140622) E
WHERE
    S_TIMESTAMP <= TO_DATE('20140620205959', 'YYYYMMDDHH24MISS')
 

Y con esto ya no muestra los Table Access Full, pero noto que el costo se ha incrementado considerablemente:
https://www.dropbox.com/s/1ochfc9jvb...t01-1.png?dl=0

No estoy seguro si es buena usar hints en esta consulta, si me confirman.

Muchas gracias, saludos.

[huesos52]

Hola eduar2083

Efectivamente puedes usar los hint indexes.

Pero mi pregunta es:
El costo que mencionas, es el que pones en la imagen?
Porque lo veo supremamnete bajito para ser tablas con union y consultando una tabla particionada que asumo tiene millones de registros.

Saludos

[eduar2083]

Hola de nuevo,
Primero mencionar que por error eliminé la imagen del primer link, se está mostrando la misma imagen en ambos links.

Iniciado por huesos52

El costo que mencionas, es el que pones en la imagen?
Porque lo veo supremamnete bajito para ser tablas con union y consultando una tabla particionada que asumo tiene millones de registros.

No, la data es de ambiente de desarrollo con unos pocos registros, la data de producción contiene un aproximado de 200 millones de registros por partición así que de seguro el costo se incrementa.

He realizado la consulta a otras particiones con mayor volumen de data. La consulta final en realidad tiene la siguiente forma:

SQL SIN HINTS

Código SQL:

SELECT
    NVL(COL_A, -1) AS COL_A,
    DM.FN_FORMATEAR_TEXT(COL_B) AS COL_B,
    NVL(COL_C, -1) AS COL_C,
    MAX(S_TIMESTAMP) CLOSING_TIME
FROM (
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160702)
    WHERE
        S_TIMESTAMP > TO_DATE('20160701205959', 'YYYYMMDDHH24MISS')
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160703      )
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160704      )
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160705      )
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160706      )
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160707      )
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160708      )
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160709      )
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160710      )
    UNION
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160711)
    WHERE
        S_TIMESTAMP <= TO_DATE('20160710205959', 'YYYYMMDDHH24MISS')
)
GROUP BY COL_A, COL_B, COL_C
SIN_HINTS.SQL
Abrir con Documentos de Google
Mostrando SIN_HINTS.SQL.
 

SQL CON HINTS

Código SQL:

SELECT
    NVL(COL_A, -1) AS COL_A,
    DM.FN_FORMATEAR_TEXT(COL_B) AS COL_B,
    NVL(COL_C, -1) AS COL_C,
    MAX(S_TIMESTAMP) CLOSING_TIME
FROM (
    SELECT
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160702) A
    WHERE
        S_TIMESTAMP > TO_DATE('20160701205959', 'YYYYMMDDHH24MISS')
    UNION
    SELECT /*+ INDEX(B IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160703      ) B
    UNION
    SELECT /*+ INDEX(C IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160704      ) C
    UNION
    SELECT /*+ INDEX(D IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160705      ) D
    UNION
    SELECT /*+ INDEX(E IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160706      ) E
    UNION
    SELECT /*+ INDEX(F IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160707      ) F
    UNION
    SELECT /*+ INDEX(G IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160708      ) G
    UNION
    SELECT /*+ INDEX(H IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160709      ) H
    UNION
    SELECT /*+ INDEX(I IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160710      ) I
    UNION
    SELECT /*+ INDEX(J IDX_GPRS_S_TIMESTAMP)*/
        COL_A,
        COL_B,
        COL_C,
        S_TIMESTAMP
    FROM DM.CDR_GPRS PARTITION(P_20160711) J
    WHERE
        S_TIMESTAMP <= TO_DATE('20160710205959', 'YYYYMMDDHH24MISS')
)
GROUP BY COL_A, COL_B, COL_C
CON_HINTS.SQL
Abrir con Documentos de Google
Mostrando CON_HINTS.SQL.
Guardando en Drive - Mover a:
 

COSTO CON HINTS:
https://drive.google.com/open?id=0B9...VpNlBLc0E3N0ZJ

Mi duda es si la consulta está optimizada o se podría mejorar,

Muchas gracias, saludos.

[huesos52]

Si depronto nos contaras un poco mas el objetivo de la información, podríamos recomendarte algo mas.

Si dices que por cada tabla particionada traes 200 millones de registros y estas uniendo 10 particiones (2000 millones de registros), No me cabe en la cabeza que podrías hacer con esos datos en el resultado de una consulta.
Independiente de que use o no indices, un resultado de este tamaño tiene que ser costoso. Mirese por donde se mire. De igual forma, es bueno que tengas en cuenta que un index full scan no es un escenario mucho mejor a un full table access. Y en tu consulta esto se da, porque no tienes condiciones en varias de las consultas con columnas que efectivamente tengan indices asociados.

De seguro si nos cuentas otro poco, podríamos enfocar el tema a otra solución mas optima.

Saludos

[eduar2083]

Hola de nuevo,

Sucede que un proveedor de servicios de internet almacena todos los accesos a la red desde un dispositivo móvil, es decir, cada vez que desde el dispositivo se consuman datos, está interacción se almacena como un registro en la BD, son millones de dispositivos sólo en mi ciudad y cada dispositivo accede muchas veces a la red internet.
La tabla donde se almacena dicha información se llama DM.CDR_GPRS y entre sus columnas tienen el identificador del dispositivo (COL_A), la fecha y hora de acceso (S_TIMESTAMP de tipo Date) entre otros datos.

Un ente regulador estatal desea conocer los últimos accesos de cada dispositivo para un rango de fechas específicas que él establece, con la condición de que la fecha inicial sólo obtenga aquellos accesos mayores a una hora específica (20:59:59) y de igual manera con la fecha final sólo aquellos accesos hasta dicha hora específica.

Ya comenté que las particiones de la tabla son diarias en base al campo S_TIMESTAMP. He implementado un procedimiento que genera es Sql dinámico que les he mostrado.

Creo que ahora ya podrían tener un mayor panorama del problema y deseo me puedan orientar.

Muchas gracias.

[huesos52]

Y el ente regulador estatal como va a manipular estos datos?
Archivo de texto? a travès de una aplicaciòn? web? escritorio? Servicio? Excel?

De igual forma, creo que si es un informe para un tercero, y su tiempo de ejecuciòn no es muy critico, tu consulta ya estarìa aceptable.

Saludos