Oracle Tips; BULK COLLECT

Normalmente los programadores utilizan un cursor para extraer y procesar múltiples registros de datos, uno a la vez, pero hay problemas de performance cuando se esta lidiando con grandes cantidades de registros usando cursores. Como sabemos, un cursor entrega un registro a la vez, manteniendo una vista consistente, hasta que todos los registros hayan sido procesados o hasta que el cursor es cerrado.

Un problema de performance aparece por el hecho de que hay dos motores «engines» en la base de datos, el PL/SQL Engine y el SQL Engine. En algunas versiones de la base de datos, esos motores «engines» tienen diferentes comportamientos debido a alguna característica disponible en SQL pero no en PL/SQL. Cuando un cursor entrega un registro de datos este ejecuta a «Context Switch» al motor «engine» de SQL, y es el componente de SQL que extrae la data. El Motor de SQL aloja la data en memoria y otro «context switch» nos lo devuelve a PL/SQL.

El motor PL/SQL continúa procesando hasta que el próximo registro es requerido, y el proceso es repetido. El conext switch es muy rápido, pero si es ejecutado una y otra vez, contantemente, esto puede tomar notable cantidad de tiempo. Un BULK COLLECT es un método de entregar datos donde el motor de PL/SQL le dice al motor de SQL que colecte muchos registros de una sola vez y lo entregue en una sola colección. El motor de SQL retrae todo los registros, lo carga en una colección y lo devuelve al motor PL/SQL. Todos los registros son extraídos con solo 2 conetext switches. Mientras mas largo es el número de registros a procesar, más eficiencia es ganada cuando se usa BULK COLLECT.

declare

   type number_array is varray(10000) of number;

   type string_array is varray(10000) of varchar2(100);

   a_store string_array;

   a_qty   number_array;

   cursor c1 is

      select store_key, sum(quantity) from sales

      group by store_key;

      begin

      open c1;

         fetch c1 bulk collect into a_store, a_qty;

      close c1;

     for indx in a_store.first..a_store.last loop

       dbms_output.put_line(a_store(indx)||’….’||a_qty(indx));

     end loop;

  end; /

 

S102….21860

S105….13000

S109….12120

S101….2180

S106….6080

S103….7900

S104….13700

S107….24700

S108….5400

S110….3610

Si queremos mejorar el performace de un select, update o un delete de grandes cantidades de registros a procesar es buena idea utilizar el BULK COLLECT combinado con el FORALL.

Como utilizar el BULK COLLECT

(i)  Input collections, usar FORALL statement

(ii) Output collections, usar BULK COLLECT clause

 

(i) Input Collections

Input collections son datos pasados del motor PL/SQL al motor SQL al ejecutar INSERT, UPDATE y DELETE.

 

Sintaxis:

 

   FORALL index IN lower_bound..upper_bound

     sql_statement;

 

Ejemplo 1

———

 

In this example, 5000 part numbers and names are loaded into index-by

tables.  Then, all table elements are inserted into a database table

twice.  First, they are inserted using a FOR loop, which completes in

8 seconds.  Then, they are inserted using a FORALL statement, which

completes in only 0 seconds.

 

SQL> SET SERVEROUTPUT ON

SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));

   

DECLARE

  TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;

   TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;

   pnums NumTab;

   pnames NameTab;

   t1 CHAR(5);

   t2 CHAR(5);

   t3 CHAR(5);

   PROCEDURE get_time(t OUT NUMBER) IS

   BEGIN SELECT TO_CHAR(SYSDATE,’SSSSS’) INTO t FROM dual; END;

BEGIN

   FOR j IN 1..5000 LOOP  — load index-by tables

      pnums(j) := j;

      pnames(j) := ‘Part No. ‘ || TO_CHAR(j);

   END LOOP;

   get_time(t1);

   FOR i IN 1..5000 LOOP — use FOR loop

      INSERT INTO parts VALUES (pnums(i), pnames(i));

   END LOOP;

   get_time(t2);

   FORALL i IN 1..5000  –use FORALL statement

      INSERT INTO parts VALUES (pnums(i), pnames(i));                         

   get_time(t3);

   DBMS_OUTPUT.PUT_LINE(‘Execution Time (secs)’);

   DBMS_OUTPUT.PUT_LINE(‘———————‘);

   DBMS_OUTPUT.PUT_LINE(‘FOR loop: ‘ || TO_CHAR(t2 – t1));

   DBMS_OUTPUT.PUT_LINE(‘FORALL:   ‘ || TO_CHAR(t3 – t2));

END;

/                                                                             

SQL> @bulk.sql

Execution Time (secs)

———————

FOR loop: 8

FORALL:   0

 

PL/SQL procedure successfully completed.              

 

Rollback Behavior

—————–

Si una sentencia FORALL falla, entonces los cambio en la base de datos son retornados a un savepoint implícito marcado antes de cada ejecución de la sentencia SQL.

(ii) Output Collections

Output collections son datos pasados del motor SQL al motor PL/SQL como resultado de una sentencia SELECT o FETCH.

 

 

Using the BULK COLLECT clause

—————————–

The keywords BULK COLLECT can be used with SELECT INTO, FETCH INTO,

and RETURNING INTO clauses.  The syntax is as follows:

 

   … BULK COLLECT INTO collection_name[, collection_name] ….

   Note:  Examples 2, 3, and 4 use EMP and DEPT tables from the

          scott/tiger schema.

 

Example 2

———

The following is an example for the SELECT INTO clause:

DECLARE

   TYPE NumTab IS TABLE OF emp.empno%TYPE;

   TYPE NameTab IS TABLE OF emp.ename%TYPE;

   enums NumTab; — no need to initialize

   names NameTab;

BEGIN

   SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;

   FOR i in enums.FIRST..enums.LAST LOOP

      DBMS_OUTPUT.PUT_LINE(enums(i) || ‘ ‘ || names(i));

   END LOOP;

END;

/                                                                             

Example 3

———

The following is an example for the FETCH INTO clause:

DECLARE

   TYPE NameTab IS TABLE OF emp.ename%TYPE;

   TYPE SalTab IS TABLE OF emp.sal%TYPE;

   names NameTab;

   sals SalTab;

   CURSOR c1 IS SELECT ename, sal FROM emp;

BEGIN

   OPEN c1;

   FETCH c1 BULK COLLECT INTO names, sals;

   FOR i IN names.FIRST..names.LAST LOOP

      DBMS_OUTPUT.PUT_LINE(names(i) || ‘ ‘ || sals(i));

   END LOOP;

   CLOSE c1;

END;

/                                                                             

Restricciones

———–

No se puede hacer un BULK-FETCH de un cursor en un collection records.                                                                     

 

New Cursor Attribute

——————–

The new composite cursor attribute for bulk binds is %BULK_ROWCOUNT.  Its

syntax is as follows:

 

   IF SQL%BULK_ROWCOUNT(i) = … THEN

      …..

   ENDIF;

 

Example 4

———

DECLARE

   TYPE NumList IS TABLE OF NUMBER;

   depts NumList := NumList(10,20,50);

BEGIN

   FORALL i IN depts.FIRST..depts.LAST

      UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);

   IF SQL%BULK_ROWCOUNT(3) = 0 THEN

   DBMS_OUTPUT.PUT_LINE(‘Its 3!!!’);

   END IF;

END;