Hola de nuevo,
Primero mencionar que por error eliminé la imagen del primer link, se está mostrando la misma imagen en ambos links.
Cita:
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:
Ver originalSELECT
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.
Código SQL:
Ver originalSELECT
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:
Mi duda es si la consulta está optimizada o se podría mejorar,
Muchas gracias, saludos.