PL/SQL: Performance: Cuando usar BULK

En varias oportunidades me he encontrado con alguien que me consulta cual es la mejor forma de utilizar las operaciones Bulk en PL/SQL. En general, la receta es: Si la operación se puede realizar en el motor SQL, hágalo allá; si necesita traer los registros al motor PL/SQL, use Bulk. Por ejemplo, si solo necesita hacer una copia de registros de una tabla a otra, usando algunas operaciones básicas, la operación INSERT/SELECT es mucho más eficiente que llevar los registros al motor PL/SQL y hacer un bulk insert:

Caso 1: Insert / Select

INSERT INTO resumen_creaciones
SELECT   TRUNC (fecha_creacion, 'MONTH') AS mes,
    tipo_objeto,
    SUM (total_objetos) AS total_mensual
FROM     objetos_creados
GROUP BY TRUNC (fecha_creacion, 'MONTH'), tipo_objeto;

Caso 2: Bulk Select / Bulk Insert

.....
SELECT   TRUNC (fecha_creacion, 'MONTH'), tipo_objeto, SUM (total_objetos)
BULK COLLECT INTO v_mes, v_tipo_objeto, v_total_mensual
FROM     objetos_creados
GROUP BY TRUNC (fecha_creacion, 'MONTH'), tipo_objeto;

FORALL i IN v_mes.FIRST .. v_mes.LAST
INSERT INTO resumen_creaciones
       (mes, tipo_objeto, total_mensual
       )
VALUES      (v_mes (i), v_tipo_objeto (i), v_total_mensual (i)
       );
....

Para el caso 1:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      2      0.00       0.00          3          8         25          38
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.01       0.00          3         11         25          39

Para el caso 2:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.01          3          5         25          38
Fetch        2      0.00       0.00          0          6          0          39
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.01          3         11         25          77

Beneficios de operaciones SQL:

1. Generalmente son mucho más simples de implementar
2. Producen menos parsing
3. El motor SQL implementa estas operaciones con muchos menos pasos
4. No hay intercambio de registros entre los motores PL/SQL y SQL
5. Más fáciles de mantener

Cuando usar PL/SQL+Colecciones+Procesos Bulk

1. Si es necesario tener el control de errores por registro (p.e para ETL)
2. Es muy grande el volumen de datos a procesar, mejor, dividir para conquistar. En este caso se puede usar BULK con LIMIT y procesar en grupos limitados de registros
3. La query o DML es demasiado compleja para mantenerla como una sola instrucción
4. Si la operación no se puede implementar solo con SQL ya que necesita instrucciones procedurales

Los ejemplos completos se pueden ver en

• Crear escenario
• eInsert_SQL.sql
• eInsert_Bulk.sql

Contexto: Oracle 10g R2 en Centos 5.2