Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Oracle »

Consulta a tabla particionada

Estas en el tema de Consulta a tabla particionada en el foro de Oracle en Foros del Web. 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 ...
  #1 (permalink)  
Antiguo 16/08/2016, 11:39
 
Fecha de Ingreso: octubre-2014
Ubicación: Lima
Mensajes: 74
Antigüedad: 9 años, 5 meses
Puntos: 1
Consulta a tabla particionada

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:
Ver original
  1. SELECT
  2.     COL_A,
  3.     COL_B,
  4.     COL_C,
  5.     S_TIMESTAMP
  6. FROM DM.CDR_GPRS PARTITION(P_20140618)
  7. WHERE
  8.     S_TIMESTAMP > TO_DATE('20140616205959', 'YYYYMMDDHH24MISS')
  9. UNION
  10. SELECT
  11.     COL_A,
  12.     COL_B,
  13.     COL_C,
  14.     S_TIMESTAMP
  15. FROM DM.CDR_GPRS PARTITION(P_20140619      )
  16. UNION
  17. SELECT
  18.     COL_A,
  19.     COL_B,
  20.     COL_C,
  21.     S_TIMESTAMP
  22. FROM DM.CDR_GPRS PARTITION(P_20140620      )
  23. UNION
  24. SELECT
  25.     COL_A,
  26.     COL_B,
  27.     COL_C,
  28.     S_TIMESTAMP
  29. FROM DM.CDR_GPRS PARTITION(P_20140621      )
  30. UNION
  31. SELECT
  32.     COL_A,
  33.     COL_B,
  34.     COL_C,
  35.     S_TIMESTAMP
  36. FROM DM.CDR_GPRS PARTITION(P_20140622)
  37. WHERE
  38.     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.
  #2 (permalink)  
Antiguo 16/08/2016, 11:45
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 16 años, 4 meses
Puntos: 2658
Respuesta: Consulta a tabla particionada

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.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #3 (permalink)  
Antiguo 16/08/2016, 15:14
 
Fecha de Ingreso: octubre-2014
Ubicación: Lima
Mensajes: 74
Antigüedad: 9 años, 5 meses
Puntos: 1
Respuesta: Consulta a tabla particionada

He agregado hints INDEX para forzar el uso del índice:
Código SQL:
Ver original
  1. SELECT
  2.     COL_A,
  3.     COL_B,
  4.     COL_C,
  5.     S_TIMESTAMP
  6. FROM DM.CDR_GPRS PARTITION(P_20140618)
  7. WHERE
  8.     S_TIMESTAMP > TO_DATE('20140616205959', 'YYYYMMDDHH24MISS')
  9. UNION
  10. SELECT /*+ INDEX(B IDX_GPRS_S_TIMESTAMP) */
  11.     COL_A,
  12.     COL_B,
  13.     COL_C,
  14.     S_TIMESTAMP
  15. FROM DM.CDR_GPRS PARTITION(P_20140619      ) B
  16. UNION
  17. SELECT /*+ INDEX(C IDX_GPRS_S_TIMESTAMP) */
  18.     COL_A,
  19.     COL_B,
  20.     COL_C,
  21.     S_TIMESTAMP
  22. FROM DM.CDR_GPRS PARTITION(P_20140620      ) C
  23. UNION
  24. SELECT /*+ INDEX(D IDX_GPRS_S_TIMESTAMP) */
  25.     COL_A,
  26.     COL_B,
  27.     COL_C,
  28.     S_TIMESTAMP
  29. FROM DM.CDR_GPRS PARTITION(P_20140621      ) D
  30. UNION
  31. SELECT /*+ INDEX(E IDX_GPRS_S_TIMESTAMP) */
  32.     COL_A,
  33.     COL_B,
  34.     COL_C,
  35.     S_TIMESTAMP
  36. FROM DM.CDR_GPRS PARTITION(P_20140622) E
  37. WHERE
  38.     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.
  #4 (permalink)  
Antiguo 16/08/2016, 16:19
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 1 mes
Puntos: 360
Respuesta: Consulta a tabla particionada

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
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #5 (permalink)  
Antiguo 16/08/2016, 20:27
 
Fecha de Ingreso: octubre-2014
Ubicación: Lima
Mensajes: 74
Antigüedad: 9 años, 5 meses
Puntos: 1
Respuesta: Consulta a tabla particionada

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 Ver Mensaje
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 original
  1. SELECT
  2.     NVL(COL_A, -1) AS COL_A,
  3.     DM.FN_FORMATEAR_TEXT(COL_B) AS COL_B,
  4.     NVL(COL_C, -1) AS COL_C,
  5.     MAX(S_TIMESTAMP) CLOSING_TIME
  6. FROM (
  7.     SELECT
  8.         COL_A,
  9.         COL_B,
  10.         COL_C,
  11.         S_TIMESTAMP
  12.     FROM DM.CDR_GPRS PARTITION(P_20160702)
  13.     WHERE
  14.         S_TIMESTAMP > TO_DATE('20160701205959', 'YYYYMMDDHH24MISS')
  15.     UNION
  16.     SELECT
  17.         COL_A,
  18.         COL_B,
  19.         COL_C,
  20.         S_TIMESTAMP
  21.     FROM DM.CDR_GPRS PARTITION(P_20160703      )
  22.     UNION
  23.     SELECT
  24.         COL_A,
  25.         COL_B,
  26.         COL_C,
  27.         S_TIMESTAMP
  28.     FROM DM.CDR_GPRS PARTITION(P_20160704      )
  29.     UNION
  30.     SELECT
  31.         COL_A,
  32.         COL_B,
  33.         COL_C,
  34.         S_TIMESTAMP
  35.     FROM DM.CDR_GPRS PARTITION(P_20160705      )
  36.     UNION
  37.     SELECT
  38.         COL_A,
  39.         COL_B,
  40.         COL_C,
  41.         S_TIMESTAMP
  42.     FROM DM.CDR_GPRS PARTITION(P_20160706      )
  43.     UNION
  44.     SELECT
  45.         COL_A,
  46.         COL_B,
  47.         COL_C,
  48.         S_TIMESTAMP
  49.     FROM DM.CDR_GPRS PARTITION(P_20160707      )
  50.     UNION
  51.     SELECT
  52.         COL_A,
  53.         COL_B,
  54.         COL_C,
  55.         S_TIMESTAMP
  56.     FROM DM.CDR_GPRS PARTITION(P_20160708      )
  57.     UNION
  58.     SELECT
  59.         COL_A,
  60.         COL_B,
  61.         COL_C,
  62.         S_TIMESTAMP
  63.     FROM DM.CDR_GPRS PARTITION(P_20160709      )
  64.     UNION
  65.     SELECT
  66.         COL_A,
  67.         COL_B,
  68.         COL_C,
  69.         S_TIMESTAMP
  70.     FROM DM.CDR_GPRS PARTITION(P_20160710      )
  71.     UNION
  72.     SELECT
  73.         COL_A,
  74.         COL_B,
  75.         COL_C,
  76.         S_TIMESTAMP
  77.     FROM DM.CDR_GPRS PARTITION(P_20160711)
  78.     WHERE
  79.         S_TIMESTAMP <= TO_DATE('20160710205959', 'YYYYMMDDHH24MISS')
  80. )
  81. GROUP BY COL_A, COL_B, COL_C
  82. SIN_HINTS.SQL
  83. Abrir con Documentos de Google
  84. Mostrando SIN_HINTS.SQL.


SQL CON HINTS
Código SQL:
Ver original
  1. SELECT
  2.     NVL(COL_A, -1) AS COL_A,
  3.     DM.FN_FORMATEAR_TEXT(COL_B) AS COL_B,
  4.     NVL(COL_C, -1) AS COL_C,
  5.     MAX(S_TIMESTAMP) CLOSING_TIME
  6. FROM (
  7.     SELECT
  8.         COL_A,
  9.         COL_B,
  10.         COL_C,
  11.         S_TIMESTAMP
  12.     FROM DM.CDR_GPRS PARTITION(P_20160702) A
  13.     WHERE
  14.         S_TIMESTAMP > TO_DATE('20160701205959', 'YYYYMMDDHH24MISS')
  15.     UNION
  16.     SELECT /*+ INDEX(B IDX_GPRS_S_TIMESTAMP)*/
  17.         COL_A,
  18.         COL_B,
  19.         COL_C,
  20.         S_TIMESTAMP
  21.     FROM DM.CDR_GPRS PARTITION(P_20160703      ) B
  22.     UNION
  23.     SELECT /*+ INDEX(C IDX_GPRS_S_TIMESTAMP)*/
  24.         COL_A,
  25.         COL_B,
  26.         COL_C,
  27.         S_TIMESTAMP
  28.     FROM DM.CDR_GPRS PARTITION(P_20160704      ) C
  29.     UNION
  30.     SELECT /*+ INDEX(D IDX_GPRS_S_TIMESTAMP)*/
  31.         COL_A,
  32.         COL_B,
  33.         COL_C,
  34.         S_TIMESTAMP
  35.     FROM DM.CDR_GPRS PARTITION(P_20160705      ) D
  36.     UNION
  37.     SELECT /*+ INDEX(E IDX_GPRS_S_TIMESTAMP)*/
  38.         COL_A,
  39.         COL_B,
  40.         COL_C,
  41.         S_TIMESTAMP
  42.     FROM DM.CDR_GPRS PARTITION(P_20160706      ) E
  43.     UNION
  44.     SELECT /*+ INDEX(F IDX_GPRS_S_TIMESTAMP)*/
  45.         COL_A,
  46.         COL_B,
  47.         COL_C,
  48.         S_TIMESTAMP
  49.     FROM DM.CDR_GPRS PARTITION(P_20160707      ) F
  50.     UNION
  51.     SELECT /*+ INDEX(G IDX_GPRS_S_TIMESTAMP)*/
  52.         COL_A,
  53.         COL_B,
  54.         COL_C,
  55.         S_TIMESTAMP
  56.     FROM DM.CDR_GPRS PARTITION(P_20160708      ) G
  57.     UNION
  58.     SELECT /*+ INDEX(H IDX_GPRS_S_TIMESTAMP)*/
  59.         COL_A,
  60.         COL_B,
  61.         COL_C,
  62.         S_TIMESTAMP
  63.     FROM DM.CDR_GPRS PARTITION(P_20160709      ) H
  64.     UNION
  65.     SELECT /*+ INDEX(I IDX_GPRS_S_TIMESTAMP)*/
  66.         COL_A,
  67.         COL_B,
  68.         COL_C,
  69.         S_TIMESTAMP
  70.     FROM DM.CDR_GPRS PARTITION(P_20160710      ) I
  71.     UNION
  72.     SELECT /*+ INDEX(J IDX_GPRS_S_TIMESTAMP)*/
  73.         COL_A,
  74.         COL_B,
  75.         COL_C,
  76.         S_TIMESTAMP
  77.     FROM DM.CDR_GPRS PARTITION(P_20160711) J
  78.     WHERE
  79.         S_TIMESTAMP <= TO_DATE('20160710205959', 'YYYYMMDDHH24MISS')
  80. )
  81. GROUP BY COL_A, COL_B, COL_C
  82. CON_HINTS.SQL
  83. Abrir con Documentos de Google
  84. Mostrando CON_HINTS.SQL.
  85. 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.

Última edición por eduar2083; 16/08/2016 a las 20:33
  #6 (permalink)  
Antiguo 17/08/2016, 07:22
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 1 mes
Puntos: 360
Respuesta: Consulta a tabla particionada

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
__________________
Without data, You are another person with an opinion.
W. Edwads Deming
  #7 (permalink)  
Antiguo 17/08/2016, 14:32
 
Fecha de Ingreso: octubre-2014
Ubicación: Lima
Mensajes: 74
Antigüedad: 9 años, 5 meses
Puntos: 1
Respuesta: Consulta a tabla particionada

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.
  #8 (permalink)  
Antiguo 17/08/2016, 21:28
Avatar de huesos52
Colaborador
 
Fecha de Ingreso: febrero-2009
Ubicación: Manizales - Colombia
Mensajes: 5.980
Antigüedad: 15 años, 1 mes
Puntos: 360
Respuesta: Consulta a tabla particionada

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
__________________
Without data, You are another person with an opinion.
W. Edwads Deming

Etiquetas: tabla
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 02:22.