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; <<bulck_collect>> 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!