tabla de histórico o índices?

[MutenRo]

Hola! soy nuevo en el foro y voy a empezar con una consulta, aunque espero poder aportar algo en cuanto pueda.

Tengo una duda acerca de cómo afrontar el problema de que una tabla (concretamente de Oracle) se vaya haciendo más y más grande, con el consiguiente retardo en las consultas.

Doy unos datos para que os hagáis la composición de lugar: en la tabla se van cargando diariamente datos (con la fecha incluida), unos 17.000 registros. Aunque actualmente hay 12.000.000 de registros en la tabla, la previsión es de un aumento aproximado de 6.000.000 de registros anuales (~17.000 · 360).

Por supuesto, hay índices creados para agilizar las consultas pero ninguno de estos índices es por fecha debido a que lo que se suele solicitar es un rango de fechas para los que hay que obtener unos datos.

Tengo que optimizar tiempos de consulta, y la duda que tengo es si crear una nueva tabla con los datos del último año completo (lo que más se consulta) y dejar todo lo demás en un histórico, o por el contrario crear nuevos índices que incluyan parte de la fecha: TO_CHAR(TABLA.FECHA, 'YYYY'), CENTRO, etc. y seguir utilizando la misma tabla de siempre aún sabiendo que tendrá un crecimiento fuerte.

¿Teneís alguna experiencia con este tipo de problemas? ¿por cuál de las dos soluciones tirariais?

Gracias de antemano.

[seyko]

Bien para empezar si se filtra por rangos de fechas un indice en el campo de las fechas ayudará!

Si el rendimiento es insuficiente puedes particionar la tabla!

Depende de si te sirve con los indices o no.

Salu2

[matanga]

Hola,

Agrego un poco mas de info, para este tipo de casos, la mejor opcion (y la mas cara) es particionamiento por rangos, el problema es que necesitas la version Enterprise de Oracle. Una vez particionada la tabla, debes crear un indice sobre el campo que maneja las fechas, y decidir si el indice tendra un particionamiento propio o se guiara por el particionamiento de la tabla.

Tambien puedes implementar un historico o un modelo de depuracion de datos, no necesariamente son opciones excluyentes.

Saludos

[MutenRo]

seyko, matanga, gracias por las respuestas.

Me gustaría saber si el particionar la tabla por años (¿sería posible? el campo fecha tiene formato DD/MM/AAAA, es tipo date), por ejemplo, afectaría negativamente a consultas que abarcan fehas de dos años distintos (desde diciembre de 2007 hasta febrero de 2008 por ejemplo). También me gustaría saber si hacer esto sería realmente más efectivo que volver a crear todos los índices añadiéndoles como campo adicional el año del campo FECHA y modificar las consultas para que siempre indiquen el año (o los años) involucrados.

La situación real es que hay consultas que lo que hacen es obtener medias o sumas de datos de todo un año completo, con lo cual tiene que recorrerse 200.000 registros por ejemplo para devolver un único registro con la media de un valor por ejemplo.

La desventaja que le veo a crear una tabla con los valores históricos y otra con los de los dos últimos años es que es mucho más costoso de mantener (duplicar los insert, update y delete de una en la otra) y al final si una consulta va a leer datos de todo un año acabará recorriéndose casi toda la tabla. ¿descartaríais la creación del histórico?

[seyko]

Si vas a sacar medias y sumas de 200.000 registros de distintos años. Indexa todo lo que puedas, pero la consulta no va a ser rapida en ningun caso.

Todavia no entiendo porque no indexas la fecha?!?!

[matanga]

Hola,

Me gustaría saber si el particionar la tabla por años (¿sería posible? el campo fecha tiene formato DD/MM/AAAA, es tipo date)

Se puede particionar por un campo fecha, es una de las practicas mas comunes del particionamiento por rangos, donde los rangos pueden ser, por ejemplo, meses años o conjunto de años.

...por ejemplo, afectaría negativamente a consultas que abarcan fehas de dos años distintos (desde diciembre de 2007 hasta febrero de 2008 por ejemplo).

Ahora habra que leer sobre particionamiento, pero la idea es que una particion se almacena como un segmento independiente (somo si fuera una tabla en si), no hay ningun problema si la consulta necesita leer de una o mas particiones, pero la idea es particionar la tabla de tal manera que la mayoria de las consultas lean una en particular.

También me gustaría saber si hacer esto sería realmente más efectivo que volver a crear todos los índices añadiéndoles como campo adicional el año del campo FECHA y modificar las consultas para que siempre indiquen el año (o los años) involucrados.

Los indices son herramientas, y deben ser creados segun la necesidad, es decir, crear indices para satisfacer a las consultas y no modificar consultas para satisfacer a los indices.

La situación real es que hay consultas que lo que hacen es obtener medias o sumas de datos de todo un año completo, con lo cual tiene que recorrerse 200.000 registros por ejemplo para devolver un único registro con la media de un valor por ejemplo.

Esto es muy propio de Data warehouse, para esto Oracle implementa Materialized Views, no tiene relacion con particionamiento.

La desventaja que le veo a crear una tabla con los valores históricos y otra con los de los dos últimos años es que es mucho más costoso de mantener (duplicar los insert, update y delete de una en la otra) y al final si una consulta va a leer datos de todo un año acabará recorriéndose casi toda la tabla. ¿descartaríais la creación del histórico?

El historico, si lo implementas con particionamiento, no tiene necesidad de duplicar insert ni delete ni ninguna otra sentencia SQL, Oracle administra las particiones como lo definas, y puedes ir moviendo las mas antiguas a Tablespaces sobre discos mas lentos, o bien, puedes exportar una particion, truncarla y recuperarla si hace falta consultar el historico.

Saludos

[MutenRo]

Buenas, antes de nada os vuelvo a agradecer las respuestas.

seyko:

Todavia no entiendo porque no indexas la fecha?!?!

En la aplicación el usuario tiene que introducir obligatoriamente un rango de fechas (FECHADESDE, FECHAHASTA) que luego se añade a la cáusula WHERE de todas las consultas de esta manera: FECHADESDE >= :fecDesde AND FECHASTA <= :fecHasta, con lo cual en principio los índices no valdrían para nada puesto que no estoy introduciendo una serie de valores concretos. La idea de añadirlo al índice consistiría en añadir a las consultas la cláusula AND TO_CHAR(TABLA.FECHADESDE, 'YYYY') = :año, teniendo en cuenta que casi todos los registros tienen FECHADESDE = FECHAHASTA y que casi todas las consultas suelen darse para rangos de fechas de un mismo año.

Como comentaba matanga, en principio los índices serían herramientas para satisfacer a las consultas, pero tengo comprobado que las consultas pueden optimizarse bastante si uno es capaz de clasificar y dividir por áreas los datos. Ejemplo: supongamos que cada centro de salud de todo el mundo tiene un código identificador único. El usuario tiene un menú donde desglosa país, provincia y centro de salud. Uno podría obtener la lista de todos los centros de salud seleccionados por el usuario y filtrar mediante esos códigos, pero si creamos campos e índices por país, provincia y código de centro, para los casos más habituales en los que el usuario selecciona países o provincias completas las consultas mejoraran, puesto que entramos, por ejemplo con un único código de país.

Se puede particionar por un campo fecha, es una de las practicas mas comunes del particionamiento por rangos, donde los rangos pueden ser, por ejemplo, meses años o conjunto de años.

Interesante, desafortunadamente no lo he hecho nunca y la versión de Oracle que tenemos en los entornos locales no es la Enterprise pero creo que en producción sí, podría solicitar que lo hicieran allí...

Esto es muy propio de Data warehouse, para esto Oracle implementa Materialized Views, no tiene relacion con particionamiento

No conocía lo de Materialized Views pero he estado mirando un poco y tengo la sensación de que al final consiste en algo así como tener en una tabla auxiliar el resultado de una consulta pesada que se irá actualizando cada cierto tiempo y que luego uno puede tirar de ella para obtener el resultado de inmediato (una especie de precálculo si no he entendido mal). La pega que le veo es que en el caso que nos ocupa los datos resultantes depende de varias selecciones que ha hecho el usuario, como es el rango de fechas, es decir, la media de un valor por ejemplo varía dependiendo del rango de fechas seleccionado, con lo cual no veo qué tipo de precálculo puede ayudar en este caso.

El historico, si lo implementas con particionamiento, no tiene necesidad de duplicar insert ni delete ni ninguna otra sentencia SQL

Entiendo entonces que lo ideal es seguir con la tabla maestra (contiene los datos históricos y los actuales) pero particionada por rangos de fechas y descartar la opción de crear una nueva tabla para 'valores actuales' donde están duplicados los datos de los dos últimos años.

Un saludo.

[seyko]

Buenas,

En la aplicación el usuario tiene que introducir obligatoriamente un rango de fechas (FECHADESDE, FECHAHASTA) que luego se añade a la cáusula WHERE de todas las consultas de esta manera: FECHADESDE >= :fecDesde AND FECHASTA <= :fecHasta, con lo cual en principio los índices no valdrían para nada puesto que no estoy introduciendo una serie de valores concretos. La idea de añadirlo al índice consistiría en añadir a las consultas la cláusula AND TO_CHAR(TABLA.FECHADESDE, 'YYYY') = :año, teniendo en cuenta que casi todos los registros tienen FECHADESDE = FECHAHASTA y que casi todas las consultas suelen darse para rangos de fechas de un mismo año.

Me has hecho dudar y he realizado unas pruebas.
No tengo grandes conocimientos de como funciona el planificador de Oracle, pero me gusta la algoritmia y me defiendo con el planificador de postgres.

Asi que te enseño unas pruebas en postgres donde se ve que aunque sea un rango de fechas el indice mejora NOTABLEMENTE el rendimiento de la consulta. Por otra parte, cosa que me parecia muy lógico, pero me has hecho dudar.

Código:

create table fecha (id serial primary key, f_desde date, f_hasta date);
insert into fecha (f_desde, f_hasta) select '01/01/2000'::date +a, '01/01/2000'::date + a*2  from generate_series(1,10000) a;

Bueno una tabla con 2 fechas y 10000 registros.
La select trae 365 registros.
select *
from fecha
where f_desde >= TO_DATE('01/01/2001', 'dd/mm/yyyy')
and f_hasta <= TO_DATE('31/12/2003', 'dd/mm/yyyy')

El planificador me da:

Código:

"Seq Scan on fecha  (cost=0.00..205.00 rows=645 width=12) (actual time=0.190..6.395 rows=365 loops=1)"
"  Filter: ((f_desde >= '2001-01-01'::date) AND (f_hasta <= '2003-12-31'::date))"
"Total runtime: 6.545 ms"

Indexo las fechas

Código:

create index indice_fecha_d ON fecha(f_desde);
create index indice_fecha_h ON fecha(f_hasta);

El planificador me da:

Código:

"Index Scan using indice_fecha_h on fecha  (cost=0.00..17.69 rows=645 width=12) (actual time=0.409..0.971 rows=365 loops=1)"
"  Index Cond: (f_hasta <= '2003-12-31'::date)"
"  Filter: (f_desde >= '2001-01-01'::date)"
"Total runtime: 1.132 ms"

El coste baja de 0..205 a 0..17
Cambia el recorrido secuencial por un Index Scan. Es decir, utiliza los indices!!!
El tiempo como ves tambien se reduce drasticamente.


Sigo sin saber porque descartas una opción que te llevaría 10 minutos probarla.

Espero que te sirva y nos cuentes.

Salu2

[MutenRo]

Gracias seyko, es cierto que un índice por fecha ayuda (cuando el rango no es muy amplio). He hecho unas pruebas y no he visto mejora en el 'explain plan', PERO ya sé por qué y he conseguido que utilice el índice por fecha cerrando el rango FECHADESDE, es decir, en las consultas tengo puesto FECHADESDE >= :fecDesde AND FECHASTA <= :fecHasta. Creando el índice con FECHADESDE no he visto mejora pero si además añado AND FECHADESDE <= :fecHasta (no hay datos que tengan FECHADESDE mayor que FECHAHASTA) entonces sí que utiliza el nuevo índice al que le he añadido el campo FECHADESDE. Con esto y las particiones creo que la mejora debería ser notable, ya veremos.

[matanga]

Hola,

En threads como estos da gusto participar.

seyko, en Oracle tambien funciona de manera similar, aun cuando no se este pidiendo por un valor en particular, el optimizador puede o no decidir utilizar el indice, los tipos mas comunes de acceso a traves de un indice son,

1. Index Unique Scan, cuando el indice es unique y el filtro de la consulta contiene el campo indexado y se lo compara con un valor, por ejemplo id=5

2. Index Range Scan, el caso que demostraste en PosgreSQL, cuando el filtro de la consulta contiene el campo indexado y se lo compara con un rago de valores, o bien un unico valor pero el indice no es unique. En este caso Oracle decide si utilizar el indice o hacer un full scan sobre la tabla, pueden hacer la prueba solicitando un rango de valores superior al 20% (aprox) de los registros de la tabla.

3. Fast Full Index Scans, un full scan sobre el indice en vez de un full scan sobre la tabla, esto sucede cuando el indice contiene las columnas del select.

MutenRo, si tienes la opcion de usar particionamiento, adelante, tiene muchas ventajas, y el optimizador de Oracle las vera tambien, el solito se da cuenta si tiene que hacer los select sobre una o mas particiones. Tambien tiene las ventajas de administracion que te comentaba antes,

1. Mover particiones a diferentes Tablespaces.
2. Truncar particiones.
3. Exchange de particiones.
4. etc.

En cuanto a las Vistas Materializadas, hay muchas mas cosas para ver, Query Rewrite es una de ellas, donde el optimizador decide en tiempo real si resolver la consulta sobre las tablas originales o ir directamente a la vista materializada, desde ya que tienes que evaluar que consultas se veran beneficiadas con esto, pero hay que destacar que es una tecnologia desarrollada para optimizar funciones de agregados (Sum, Count, etc) y joins sobre grandes tablas.

Saludos

[seyko]

Iniciado por matanga

Hola,

En threads como estos da gusto participar.

Desde luego!

2. Index Range Scan, el caso que demostraste en PosgreSQL, cuando el filtro de la consulta contiene el campo indexado y se lo compara con un rago de valores, o bien un unico valor pero el indice no es unique. En este caso Oracle decide si utilizar el indice o hacer un full scan sobre la tabla, pueden hacer la prueba solicitando un rango de valores superior al 20% (aprox) de los registros de la tabla.

En postgres se que lo hace teniendo en cuenta el numero estimado de registros y un parametro % que le defines.
En Oracle seguro que hay mil parametros para esto .
Deje Oracle en la 8i cuando me pase a software libre y se que ha cambiado mucho.


Un saludo