Popola tabella target da una tabella sorgente (BULK COLLECT e FORALL)

Occorre popolare alcune tabelle dall'ambiente di produzione (sorgente) a quello di test (target).
In ambiente di test creiamo un db link tra i due schema che hanno le stesse tabelle.

In ambiente di test abbiamo creato l'utente " testimp"  e creato il database link  "dbltest" verso l'utente  prodimp di produzione.

OWNER	DB_LINK	USERNAME	HOST
TESTIMP	DBLTEST.WORLD	PRODIMP	PROD01

In ambiente di produzione abbiamo creato l'utente "prodimp" e due tabelle A e A1. Le stesse sono state poi create poi in ambiente di test.

La tabella da caricare in ambiente di tet contiene 10010 record.

La procedura ha come parametri in ingresso il numero di righe da fa leggere dalla istruzione fetch bulk collect e ogni quante righe lette effettuare la commit.

CREATE OR REPLACE PROCEDURE TESTIMP.popolatabellabulk2 ( righe IN NUMBER, ncommit IN NUMBER)
IS
v_error_code NUMBER;
v_error_msg VARCHAR2(512);
TYPE a_rt IS TABLE OF a1@dbltest%ROWTYPE;
l_a   a_rt;

CURSOR a_cur IS SELECT * FROM a1@dbltest;
vcommit NUMBER := 0;
     
BEGIN
execute immediate ('truncate table testimp.a1');
   OPEN a_cur;
   LOOP
   FETCH a_cur BULK COLLECT INTO l_a LIMIT righe;
   dbms_output.put_line('letti: '||l_a.COUNT);
        FORALL indx IN 1 .. l_a.COUNT
        INSERT INTO A1 VALUES l_a(indx);
 
        vcommit := vcommit + l_a.COUNT;
 
        if vcommit >= ncommit THEN
           commit;
           dbms_output.put_line('commit ogni '||vcommit || 'righe');
           vcommit := 0;
        end if;     
   EXIT WHEN a_cur%NOTFOUND;   
   END LOOP;
   commit;
   dbms_output.put_line('commit finale');
   
   EXCEPTION
    WHEN OTHERS THEN BEGIN
        v_error_code := SQLCODE;
        v_error_msg := SQLERRM;
        IF a_cur%ISOPEN THEN CLOSE a_cur;
        END IF;
        raise_application_error(-20001,'popolatabellabulk2-ERRORE -  '||SQLCODE||' -ERROR- '||SQLERRM);
    END;   
   CLOSE a_cur;
END popolatabellabulk2;
/


SQL> set serveroutput on;
SQL> set timing on
SQL> execute popolatabellabulk2(5000,5010);
letti: 5000
letti: 5000
commit ogni 10000righe
letti: 10
commit finale

PL/SQL procedure successfully completed.

Con questo semplice esempio possiamo verificare come si riducono i tempi di esecuzione dell'inserimento di righe in tabella utilizzando una bulk collect rispetto alla semplice cursor - fetch.
Nel primo esempio la procedura legge 5000 righe alla volta (tramite la bulk collect) ed effettua una commit ogni 20000 righe.
Nel secondo esempio invece la procedura legge una riga alla volta come se fosse un cursore classico ed effettua una commit ogni 20000 righe.


SQL> execute popolatabellabulk2(5000,20000);
letti: 5000
letti: 5000
letti: 10
commit finale
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05

Se invece passo come parametro righe=1 equivale a non sfruttare il comando bulk ma ad utilizzare una classica fetch perchè viene letto una singola riga alla volta dal cursrore.

SQL> execute popolatabellabulk2(1,20000);
...................

commit finale

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.35