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:
- Generalmente son mucho más simples de implementar
- Producen menos parsing
- El motor SQL implementa estas operaciones con muchos menos pasos
- No hay intercambio de registros entre los motores PL/SQL y SQL
- Más fáciles de mantener
Cuando usar PL/SQL+Colecciones+Procesos Bulk
- Si es necesario tener el control de errores por registro (p.e para ETL)
- 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
- La query o DML es demasiado compleja para mantenerla como una sola instrucción
- Si la operación no se puede implementar solo con SQL ya que necesita instrucciones procedurales
Los ejemplos completos se pueden ver en
Contexto: Oracle 10g R2 en Centos 5.2