=====In Oracle PL/SQL das BULK COLLECT Feature einsetzen=====
**ab min 9i?**
Mit PL/SQL **"Bulk Collect"** läßt die Performance von PL/SQL Routinen, die SQL für DML Operationen einsetzen zum Teil dramatisch beschleunigen.
Normalerweise muss für jede SQL Ausführung in PL/SQL zwischen der PL/SQL Engine und der SQL Engine hin und her gesprungen werden.
Wird zum Beispiel ein normales update Statment 100.000 über eine Loop aufgerufen muss die Pl/SQL Engine min. 100.000 mal hin und her schalten.
Bei einem Bulk Collect / Forall wird der ganze zu verarbeitende Block übergeben komplett übergeben und damit das System erheblich entlastet.
===Auslesen der Daten mit einem Bulk Collect===
==Alles auf einmal holen==
..
type cur_typ is ref cursor;
type driver_id is table of varchar2 (9) index by binary_integer;
v_cur cur_typ;
v_driverids driver_id;
..
open v_cur for p_alternativ_getid_sql using p_key;
fetch v_cur bulk collect into v_driverids
close v_cur;
==In einzelnen Blöcken holen - LIMIT verwenden ==
..
open v_cur for p_alternativ_getid_sql using p_key;
loop
fetch v_cur bulk collect into v_driverids limit 10000;
--
-- do something with the data
--
EXIT WHEN v_driverids.COUNT = 0;
end loop;
close v_cur;
* Bei jeden Fetch werden nun 10000 Datensätze geladen
* Die Collection wird jedes mal dazu zurückgesetzt und fängt bei 1 neu an
Ein einer normalen Cursor Loop würde der exist ja bei "exit when v_cur%notfound" erfolgen,
hier ist das aber dann nicht richtig im Einsatz!
Die Loop muss mit einem **EXIT WHEN v_driverids.COUNT = 0;** sonst gehen einen die letzten Daten in der Loop verloren!
===Schreiben der Daten mit einem FORALL===
Um nun die Daten wieder in eine andere Tabelle zu schreiben kann das FORALL Statement verwendet werden.
Beispiel:
forall i in 1 .. v_driverids.COUNT
execute immediate p_alternativ_insert_sql using v_driverids (i);
Sehr praktisch bei Daten Migrationen ist dabei das Feature bei Exceptions diese zu Speichern und trotzdem weiter das SQL abzuarbeiten, später kann dann auf die Exception reagiert werden.
begin
forall i in 1 .. v_driverids.COUNT save exception
execute immediate p_alternativ_insert_sql using v_driverids (i);
v_errors := v_errors + sql%bulk_exceptions.count;
v_count := v_count + sql%rowcount;
dbms_output.put_line ('-- Info ::SUCCESS rows::' || v_count );
dbms_output.put_line ('-- Info ::Error rows::' || v_errors);
-- commit the worked records
commit;
exception
when others
then
-- get the number of errors in the exception array
v_errors := v_errors + sql%bulk_exceptions.count;
v_message := sqlerrm;
dbms_output.put_line ('-- Error::' || v_message);
dbms_output.put_line ('-- Error rows::' || v_errors);
for i in 1 .. v_errors
loop
v_err_i := sql%bulk_exceptions (i).error_index;
dbms_output.put_line ('-- Info :: Key=' || v_driverids (v_err_i) || ' :: Error =' || sqlerrm (-1 * sql%bulk_exceptions(i).error_code) );
end loop;
end;
----
=== Ein kompletter Test im Vergleich===
Beispiel Code auf dem HR Demo Schema:
==Demo daten erstellen==
create table EMPLOYEES2 as select * from hr.EMPLOYEES;
-- create some data
begin
for i in 1..10
loop
insert into EMPLOYEES2 select * from EMPLOYEES2;
commit;
end loop;
end;
/
==Die Test Procedure anlegen==
create or replace procedure bulck_load_test (p_testcase in pls_integer
, p_key in varchar2 )
as
type cur_typ is ref cursor;
type driver_id is table of varchar2 (9) index by binary_integer;
v_cur cur_typ;
v_driverids driver_id;
-- mit bind variablen
p_sql varchar2 (200) := 'update EMPLOYEES2 set SALARY = SALARY*0.19 where DEPARTMENT_ID = :key';
p_getid_sql varchar2 (200) := 'select EMPLOYEE_ID from EMPLOYEES2 where DEPARTMENT_ID like :key group by EMPLOYEE_ID order by 1';
p_insert_sql varchar2 (200) := 'update EMPLOYEES2 set SALARY = SALARY*0.19 where EMPLOYEE_ID = :key';
v_count pls_integer := 0;
v_errors pls_integer := 0;
v_message varchar2 (100) := '';
v_time_start number := dbms_utility.get_time;
v_time_stop number := 0;
v_err_i pls_integer := 0;
begin
-- A) try in one statement: UPDATE als 1 SQL Statement
if p_testcase = 1
then
execute immediate p_sql using p_key;
v_count := sql%rowcount;
commit;
dbms_output.put_line ('-- Info :: SUCCESS rows ::' || v_count);
-- B) try as loop: UPDATE als Loop with Ref Cursor
elsif p_testcase = 2
then
open v_cur for p_getid_sql using p_key;
<>
loop
v_count := 0;
v_errors := 0;
fetch v_cur
bulk collect into v_driverids
limit 10000;
--
dbms_output.put_line ('-- Info ::read cound ids ::' || v_driverids.count);
--
-----------
-- EXIT wenn Array leer!!
EXIT WHEN v_driverids.COUNT = 0;
------------
-- Verarbeitung
begin
--forall i in v_driverids.first .. v_driverids.last save exceptions -- sichere die Ausnahme, mache aber weiter!
-- execute immediate p_alternativ_insert_sql using v_driverids (i);
forall i in 1 .. v_driverids.COUNT save exceptions
execute immediate p_insert_sql using v_driverids (i);
v_errors := v_errors + sql%bulk_exceptions.count;
v_count := v_count + sql%rowcount;
dbms_output.put_line ('-- Info ::SUCCESS rows::' || v_count);
dbms_output.put_line ('-- Info ::Error rows::' || v_errors);
-- add a commit each 10000
commit;
exception
when others
then
-- get the number of errors in the exception array
v_errors := v_errors + sql%bulk_exceptions.count;
v_message := sqlerrm;
dbms_output.put_line ('-- Error::' || v_message);
dbms_output.put_line ('-- Error rows::' || v_errors);
for i in 1 .. v_errors
loop
v_err_i := sql%bulk_exceptions (i).error_index;
dbms_output.put_line ('ID=' || v_driverids (v_err_i) || ' error=' || sqlerrm (-1 * sql%bulk_exceptions (i).error_code));
end loop;
end;
end loop bulck_collect;
if v_cur%isopen = true
then
close v_cur;
end if;
-- C) all in one
else
open v_cur for p_getid_sql using p_key;
fetch v_cur
bulk collect into v_driverids; -- alles auf einmal einlesen
dbms_output.put_line ('-- Info :: read cound ids ::' || v_driverids.count);
begin
forall i in 1 .. v_driverids.count save exceptions
execute immediate p_insert_sql using v_driverids (i);
v_errors := v_errors + sql%bulk_exceptions.count;
v_count := v_count + sql%rowcount;
dbms_output.put_line ('-- Info ::SUCCESS rows::' || v_count);
dbms_output.put_line ('-- Info ::Error rows::' || v_errors);
exception
when others
then
-- get the number of errors in the exception array
v_errors := v_errors + sql%bulk_exceptions.count;
v_message := sqlerrm;
dbms_output.put_line ('-- Error::' || v_message);
dbms_output.put_line ('-- Error rows::' || v_errors);
for i in 1 .. v_errors
loop
v_err_i := sql%bulk_exceptions (i).error_index;
dbms_output.put_line (
'ID=' || v_driverids (v_err_i) || ' error=' || sqlerrm (-1 * sql%bulk_exceptions (i).error_code));
end loop;
end;
commit;
close v_cur;
end if;
v_time_stop := dbms_utility.get_time;
dbms_output.put_line ( '-- Info :: Variante: '
|| p_testcase
|| ' - Zeit:'
|| to_char (round ( (v_time_stop - v_time_start) / 100, 2 ))
|| ' sec');
end bulck_load_test;
===Testlauf===
SQL>exec bulck_load_test(1,50)
-- Info :: SUCCESS rows ::92160
-- Info :: Variante: 1 - Zeit:1.33 sec
PL/SQL procedure successfully completed.
SQL>exec bulck_load_test(2,50)
-- Info ::read cound ids ::20
-- Info ::SUCCESS rows::40960
-- Info ::Error rows::0
-- Info ::read cound ids ::20
-- Info ::SUCCESS rows::40960
-- Info ::Error rows::0
-- Info ::read cound ids ::5
-- Info ::SUCCESS rows::10240
-- Info ::Error rows::0
-- Info ::read cound ids ::0
-- Info :: Variante: 2 - Zeit:2.33 sec
PL/SQL procedure successfully completed.
SQL>exec bulck_load_test(3,50)
-- Info :: read cound ids ::45
-- Info ::SUCCESS rows::92160
-- Info ::Error rows::0
-- Info :: Variante: 3 - Zeit:2.42 sec
PL/SQL procedure successfully completed.
Wie erwartet das reine SQL am schnellsten
Allerdings muss beachtet werden das wir hier nur auf einer sehr kleinen Datenmenge arbeiten, sollen >>1M Zeilen upgedated werden, spielt oft der verfügbarer Rollback Tablespace Platz nicht mehr mit!
Dann muss wieder Blockweise gearbeitet werden!
===== Quellen =====
Web:
* http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html
* http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html