Transacciones parciales en PLSQL para lidiar con tablas muy grandes o voluminosas

Hace unas semanas alguien mencionó en un comentario que en su base de datos Oracle tenía una tabla con millones de registros y que, utilizando un bucle PLSQL, pensaba ejecutar un UPDATE y un COMMIT por cada, digamos, 500 registros procesados en dicha tabla, evitando así posibles problemas con los segmentos de rollback. El caso es que dicho lector me preguntaba si yo tenía alguna sugerencia al respecto. Este tipo de problema es, ciertamente, algo más complejo de lo que a simple vista parece, y sobre el que conviene escribir con cierta calma.

Si dividimos una transacción muy larga en muchas otras más pequeñas, existen bastantes probabilidades de que a mitad de la transacción global se produzca un error ORA-01555 (snapshop too old), problema generalmente causado por la alta frecuencia en la ejecución de sentencias COMMIT, o que simplemente se produzca un fallo del sistema. Entonces nos encontraremos con una transacción realizada parcialmente. Por lo tanto, antes de implementar este tipo de solución, deberemos asegurarnos de la que la transacción global puede re-ejecutarse. La cuestión es que en muchos casos esto no será posible y, por tanto, tendremos que escribir bastante código PL/SQL para hacer posible esta re-ejecución.

Supongamos que tenemos el siguiente bloque de código PLSQL:

DECLARE
CURSOR cur IS SELECT * FROM tmillon;
BEGIN
OPEN cur;
LOOP
FETCH cur BULK COLLECT INTO cur_rec LIMIT 500;
...
FORALL i IN 1 .. cur_rec.COUNT
UPDATE tmillon SET ... ;
-- cur_rec se usa en el UPDATE
...
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;

En el caso concreto de este bloque PL/SQL os puedo asegurar de que la probabilidad de que se produzca un error ORA-01555 es bastante elevada, la razón principal es que estamos leyendo y modificando la misma tabla y que el SELECT del cursor debe entregar, en todo instante, los valores del momento en que se inició la consulta. El caso es que la base de datos necesitará espacio para el SELECT y para poder hacer un ROLLBACK de los UPDATE ejecutados, siendo muy probable que se produzca el mencionado error.

Entonces, puesto que se puede producir este error, deberemos pensar en alguna forma de poder re-ejecutar el bloque PL/SQL, necesitaremos bien una columna en la tabla que nos indique que dicho registro ha sido actualizado, u otra tabla en la que se inserten las claves primarias de los registros que han sido modificados. Si empleamos esta última solución, nuestro cursor quedaría de la siguiente manera:

CURSOR cur IS SELECT * FROM tmillon t
WHERE NOT EXISTS (
SELECT 'Existe'
FROM claves_ok c
WHERE c.prclave = t.prclave);

Estás dos soluciones no son únicas, pero lo que yo quiero hacer patente es que, en cualquier caso, siempre tendremos que escribir algo de código adicional.

Para lidiar con este tipo de situaciones yo recomiendo:

• Actualizar la tabla utilizando una sola sentencia PLSQL. Esta es, sin duda, la manera más eficiente en términos de utilización de los recursos de la base de datos. Además, no nos debemos preocupar demasiado por un posible problema con los segmentos de rollback, ya que estos se necesitan para que la transacción se complete correctamente.
• Utilizar el paquete PL/SQL DBMS_REDEFINITION. Con este paquete crearemos una copia de la tabla base de forma online, es decir, que no será necesario bloquear las modificaciones de la tabla, y, después, utilizaremos dicha tabla en el cursor principal. Estaremos utilizando una estructura de datos más compacta e incluso, si nos interesa, en la versión 10g de la base de datos Oracle, podemos añadir un comando ORDER BY a la redefinición de la tabla para reordenar los registros de la misma en el disco. De este paquete es muy probable que hable en una entrada posterior.
• Utilizar el comando CREATE TABLE AS SELECT. Esto sería similar a utilizar el paquete PL/SQL DBMS_REDEFINITION pero, en este caso, no debemos permitir que mientras se realice esta operación se esté modificando la tabla fuente original. Además, el paquete DBMS_REDEFINITION se encarga de la creación automática de todo los objetos necesarios para crear una copia de la tabla (índices, permisos, etcétera), mientras que si utilizamos CREATE TABLE AS SELECT, de ser necesario, tendremos que crear todos estos objetos manualmente.