Hola,
Cita: 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.
Cita: ...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.
Cita: 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.
Cita: 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.
Cita: 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