Mejorar el rendimiento de una actualización con Bulk Collect

Hace unos días nos llegó una solicitud para mejorar el rendimiento de una actualización que corre sobre una tabla relativamente grande, de alrededor de 350 Gb, particionada pero esta actualización en específico no está optimizada para hacer uso de la estructura ni los índices existentes.

Usualmente iniciamos por hacer una tabla intermedia de manera temporal, sobre la que corre un cursor que va lanzando actualizaciones.

Se le rebajó algo de tiempo pero recordé que se puede optimizar este tipo de operaciones utilizando una operación que hace uso de las colecciones conocida como bulk collect.

Para utilizar el bulk collect, se necesita primero crear una variable de tipo tabla donde se meterán los datos llave a utilizar para la actualización.

Bulk collect tiene una opción llamada limit, que limita la cantidad de registros que se guardan en la colección para no acabarnos la memoria de programa en caso de ser millones de registros.

Después de esto, se utiliza una operación llamada foreach, que realizará la operación (inserción o actualización) para cada registro almacenado en la colección.

Todo esto dentro del bucle del cursor, pero mejor un ejemplo para dejar esto claro:

Código Original

DECLARE
  col1 tabla_grande.columna1%TYPE;
  col2 tabla_grande.columna2%TYPE;
FOR reg_sel IN 
  (SELECT rowid, columna1, columna2 FROM tabla_grande 
  ORDER BY columnas)
LOOP
  col1 := calculos(col1, col2);
  UPDATE tabla_grande SET columna1=col1
    WHERE rowid = reg_sel.rowid;
  COMMIT;
END LOOP;
/

Código Modificado

CREATE OR REPLACE PROCEDURE procesamiento_masivo
AS
  TYPE tipo_rowid IS TABLE OF ROWID;
  TYPE tipo_col1 IS TABLE OF tabla_grande.columna1%TYPE;
  TYPE tipo_col2 IS TABLE OF tabla_grande.columna2%TYPE;
  CURSOR cur_sel IS
SELECT rowid, col1, col2
      FROM tabla_enorme;
  tabla_rowid tipo_rowid;
  tabla_col1 tipo_col1;
  tabla_col2 tipo_col2;
  limite NUMBER := 100;
BEGIN
  OPEN cur_sel;
  LOOP
    FETCH cur_sel BULK COLLECT
      INTO tabla_rowid, tabla_col1, tabla_col2 LIMIT limite;
    EXIT WHEN tabla_rowid.count = 0;

    FOR i IN 1 .. tabla_rowid.count
    LOOP
      tabla_col1(i) := calculos(tabla_col1(i), tabla_col2(i));
    END LOOP;
    FORALL i IN 1 .. tabla_rowid.count
      UPDATE tabla_grande
         SET col1 = tabla_col1(i)
       WHERE rowid = tabla_rowid(i);
  END LOOP;
  CLOSE cur_sel;
END;
/