Cláusula BULK COLLECT para mejorar el rendimiento al realizar procesamiento masivo

Yo siempre he dicho que cuando para hacer algo se pueden utilizar sentencias SQL sencillas, no resulta conveniente emplear complicados procedimientos PL/SQL que implementen la misma solución. Sin embargo, hay situaciones en que para mejorar el rendimiento de determinados bucles FOR en los que se realizan actualizaciones masivas sobre una determinada tabla de la base de datos Oracle, resulta conveniente utilizar técnicas PLSQL de procesamiento masivo (lo que en inglés se denomina BULK COLLECT).

Para entender mejor en qué consiste esta técnica, primero hay que comprender los motivos por los que un simple bucle FOR puede generar importantes problemas de rendimiento. Veamos el siguiente código PL/SQL:

FOR selrec IN 
  (SELECT * FROM tabla_enorme 
  ORDER BY muchas columnas)
LOOP
  -- Gran cantidad de código que omito y al final:
  UPDATE tabla_enorme SET ...
    WHERE clave_primaria = selrec.clave_primaria;
  COMMIT;
END LOOP;

El presente código es un extracto de un código PLSQL que uno de los lectores de este blog me envió por correo electrónico indicándome que presentaba graves problemas de rendimiento, cosa que desde un primer momento a mi no me extrañó, considerando que la tabla tabla_enorme contenía más de 30 millones de registros. Es el típico ejemplo de código PL/SQL que para mejorar su rendimiento necesita que se aplique la técnica o funcionalidad de BULK COLLECT (que traducido directamente a castellano sería similar a decir "recogida a granel" pero que aquí traduciremos por procesamiento masivo).

Antes de profundizar en el tema, debemos considerar que si leemos los datos de una base de datos Oracle sin necesidad de enviar datos de vuelta a la misma base de datos, no es necesario aplicar la técnica del BULK COLLECT. Es decir, el siguiente código PL/SQL es perfectamente utilizable y no debería generar problemas de rendimiento:

FOR x IN 
  (SELECT * FROM tabla_enorme t WHERE ...)
LOOP
  DBMS_OUTPUT.PUT_LINE (x.col1||...||x.colN);
END LOOP;

En el ejemplo vemos que estamos leyendo los datos utilizando un SELECT, pero que, aun tratándose de una operación registro a registro, no se utilizan para ser retornados a la base de datos utilizando un INSERT, UPDATE o DELETE. La sentencia SQL SELECT ya utiliza, de por sí, la funcionalidad de procesamiento masivo, ya que, desde la versión 10g de la base de datos Oracle, el código "FOR x IN (SELECT ...)", aunque no sea visible para el desarrollador, ha sido optimizado para almacenar internamente en matrices de 100 registros los resultados que devuelve dicho SELECT.

Sin embargo, veamos que ocurre con este otro ejemplo de código PL/SQL:

FOR x IN 
  (SELECT clave_primaria, col1, col2 
  FROM tabla_enorme)
LOOP
  x.col1 := calculos(x.col1, x.col2);
  UPDATE tabla_enorme
    SET col1 = x.col1
    WHERE clave_primaria = x.clave_primaria;
  COMMIT;
END LOOP;

En este código PLSQL, la sentencia SELECT estará utilizando la funcionalidad de procesamiento masivo incorporada con la versión 10g, pero no ocurrirá lo mismo con el procesamiento de la sentencia UPDATE. La pregunta ahora es, ¿cómo podemos mejorar el rendimiento de un código tan sencillo? El primer cambio que realizaremos es algo bastante sencillo:

FOR x IN
  (SELECT rowid, col1, col2 
  FROM tabla_enorme)
LOOP
  x.col1 := calculos(x.col1, x.col2);
  UPDATE tabla_enorme
    SET col1 = x.col1
    WHERE rowid = x.rowid;
END LOOP;

El nuevo código lee cada registro de la tabla_enorme, realiza algún tipo de procesado de los datos y, finalmente, realiza el UPDATE de un campo de la misma tabla por ROWID. ¿Qué hemos conseguido con respecto al primer código?, habremos evitado realizar 30 millones de veces un UNIQUE SCAN sobre el índice de la clave primaria, ya que estaremos accediendo a la tabla por ROWID. Acceder a la tabla a través del índice de la clave primaria puede implicar de tres a cinco operaciones de entrada/salida (I/O) por iteración, por lo que acceder a la tabla por ROWID en una tabla tan grande nos puede ahorrar más de cien millones de operaciones de entrada/salida.

Otro cambio que observaréis es que hemos eliminado el COMMIT después de cada iteración. Aparte de que realizar un COMMIT después de que cada registro es procesado resultará lento y reducirá bastante el rendimiento, si en mitad del procesamiento se produce algún error, habremos dejado la base de datos Oracle en un estado bastante inconsistente, por no decir corrupto (con media tabla_enorme actualizada y la otra sin actualizar).

Pero esto que hemos hecho no tiene en realidad nada que ver con la técnica o funcionalidad de procesamiento masivo. Veamos el siguiente código PL/SQL en el que hemos, por fin, utilizado un FETCH con la cláusula BULK COLLECT:

CREATE OR REPLACE PROCEDURE procesamiento_masivo
AS
  TYPE matriz_rowid IS TABLE OF ROWID;
  TYPE matriz_col1 IS TABLE OF tabla_enorme.col1%TYPE;
  TYPE matriz_col2 IS TABLE OF tabla_enorme.col2%TYPE;

  CURSOR cur IS SELECT rowid, col1, col2
    FROM tabla_enorme;
  m_rowid matriz_rowid;
  m_col1 matriz_col1;
  m_col2 matriz_col2;
  contador NUMBER := 100;

BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT
      INTO m_rowid, m_col1, m_col2 LIMIT contador;
    FOR i IN 1 .. m_rowid.count
    LOOP
      m_col1(i) := calculos(m_col1(i), m_col2(i));
    END LOOP;
    FORALL i IN 1 .. m_rowid.count
      UPDATE tabla_enorme
        SET col1 = m_col1(i)
        WHERE rowid = m_rowid(i);
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;

El nuevo código utiliza si ningún tipo de restricción la funcionalidad de procesamiento masivo BULK COLLECT, de manera que los registros se procesan de cien en cien (valor que podremos cambiar con sólo asignar un valor diferente a la variable contador). Y, una vez procesados, lo que hacemos es un UPDATE masivo (bulk update) utilizando la sentencia PL/SQL FORALL.

Es fácil observar que el código PLSQL que utiliza la funcionalidad BULK COLLECT es bastante más complicado y mucho menos intuitivo que el original, pero los resultados a nivel de rendimiento van a ser realmente sorprendentes. Para tablas con 30 millones de registros, utilizar la funcionalidad de procesamiento masivo puede hacer que nuestro código se ejecute entre diez y veinte veces más rápido que sin utilizar dicha funcionalidad (ojo que, a veces, la mejora de rendimiento puede ser incluso hasta superior).