Cita: - Sabemos a ciencia cierta que hay apenas algunos miles de registros entre fechaHasta y el registro más antiguo (alrededor de 47.000).
Esto no es un factor decisivo, la cantidad resultante de registros solo afecta al fetch (que en este caso, por la función count(), es de 1 registro), pero no afecta para nada al costo de resolver la consulta, por ejemplo, un fullscan que devuelva 1 registro puede tardar más que un indexscan que devuelva 100 registros.
Cita: - Si enviamos a buscar el rango como lo pongo en el primer caso, nos devuelve el dato instantáneamente (0,44 segundos).
- Si lo enviamos a buscar por "< fechahasta", se cae por snapshot too old, luego de varios minutos.
Aquí hay dos temas, por un lado, los tiempos en resolver cada consulta, que según comentas, en ambos casos se utiliza el índice sobre el campo Tiempo, pero seguramente sea con diferentes operaciones (ej: index range-scan para la primera e index full-scan para la segunda), por lo que deberías postear el plan de ejecución para poder compararlas, y por otro lado, el error snapshot too old, donde la probabilidad de que ocurra aumenta a mayor cantidad de registros leídos o mayor tiempo en resolver la consulta.
La operación index full-scan en la segunda consulta podría explicar el mayor tiempo de respuesta, y teniendo en cuenta que a los efectos de transacciones es lo mismo que un table full-scan, también podría explicar el error ORA-01555.
Cita: - Si envío a buscar una fecha anterior al más antiguo que exista, caso en que debería devolver NULL, se cae por snapshot too old, luego de varios minutos.
Entonces la pregunta es: ¿Por qué pierde tiempo buscando un dato que no existe (la primera entrada del indice es mayor a ese valor), y por qué, en el segundo caso, pese a tener pocos registros (hay entradas, pero son pocas), tarda tanto y se cae?
El optimizador sabe el valor mínimo o máximo de una columna por los histogramas en la vista ALL_TAB_COL_STATISTICS (campos HIGH_VALUE y LOW_VALUE), si la tabla Log_Sistema tiene inserciones constantes, es probable que esta información esté desactualizada, y por lo tanto, la condición WHERE Tiempo < Valor_Mínimo se tenga que resolver con un index full-scan, en este caso, la solución seria aumentar la frecuencia en que se calculan las estadísticas (dbms_stats.gather_table_stats)
Cita: Es ese punto el que tenemos que resolver: Necesitamos que no pierda tiempo leyendo si no tiene registros o bien, que tarde poco si los tiene.
Primero hay que tener en mente una premisa, los procesos de depuración no son compatibles con OLTP, y por lo tanto, se recomienda ejecutarlos en horarios nocturnos o de poca carga de trabajo, dicho esto, la soluciones pueden ser varias:
1. Si el rendimiento no importa, y el problema es el ORA-01555, lo fácil es incrementar el tablespace de undo y el parámetro undo_retention (compatible con OLTP).
2. Si el problema es el rendimiento y además el ORA-01555, tienes las opciones (no tan compatibles con OLTP por los table lock):
2.1. Utilizar Table Partitioning por rangos sobre el campo Tiempo, esto hace que el proceso de depuración sea principalmente de sentencias DDL como alter table Log_Sistema add partition y alter table Log_Sistema truncate partition.
2.2. Utilizar una tabla temporal, por ejemplo:
Código:
create global temporary temp_log_sistema
as select * from log_sistema
/
create index idx_tiempo on temp_log_sistema(tiempo)
/
truncate table log_sistema
/
insert into log_sistema
select * from temp_log_sistema
where tiempo >= fechaHasta
/
3. Utilizar un trigger sobre la tabla Log_Sistema para que en cada insert busque los registros menores a fechaHasta y los mueva a una tabla Log_Sistema_Historico (depuración online y compatible con OLTP).
Saludos