prog:plsql_bulk_collect
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:plsql_bulk_collect [2016/05/20 17:37] – [Schreiben der Daten mit einem FORALL] gpipperr | prog:plsql_bulk_collect [2016/05/20 17:39] (aktuell) – [Ein Demo] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====In Oracle PL/SQL das BULK COLLECT Feature einsetzen===== | ||
+ | **ab min 9i?** | ||
+ | |||
+ | |||
+ | Mit PL/SQL **" | ||
+ | |||
+ | 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== | ||
+ | <code sql> | ||
+ | .. | ||
+ | type cur_typ is ref cursor; | ||
+ | type driver_id is table of varchar2 (9) index by binary_integer; | ||
+ | v_cur cur_typ; | ||
+ | v_driverids | ||
+ | |||
+ | .. | ||
+ | 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 == | ||
+ | |||
+ | <code sql> | ||
+ | .. | ||
+ | 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" | ||
+ | hier ist das aber dann nicht richtig im Einsatz! | ||
+ | |||
+ | Die Loop muss mit einem <fc # | ||
+ | |||
+ | |||
+ | ===Schreiben der Daten mit einem FORALL=== | ||
+ | |||
+ | Um nun die Daten wieder in eine andere Tabelle zu schreiben kann das FORALL Statement verwendet werden. | ||
+ | |||
+ | Beispiel: | ||
+ | <code sql> | ||
+ | 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, | ||
+ | |||
+ | <code sql> | ||
+ | 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 | ||
+ | |||
+ | dbms_output.put_line ('-- Info ::SUCCESS rows::' | ||
+ | dbms_output.put_line ('-- Info ::Error rows::' | ||
+ | -- 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::' | ||
+ | dbms_output.put_line ('-- Error rows::' | ||
+ | for i in 1 .. v_errors | ||
+ | loop | ||
+ | | ||
+ | | ||
+ | end loop; | ||
+ | end; | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | |||
+ | === Ein kompletter Test im Vergleich=== | ||
+ | |||
+ | |||
+ | Beispiel Code auf dem HR Demo Schema: | ||
+ | |||
+ | ==Demo daten erstellen== | ||
+ | |||
+ | <code sql> | ||
+ | create table EMPLOYEES2 as select * from hr.EMPLOYEES; | ||
+ | -- create some data | ||
+ | begin | ||
+ | for i in 1..10 | ||
+ | loop | ||
+ | | ||
+ | commit; | ||
+ | end loop; | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | </ | ||
+ | |||
+ | ==Die Test Procedure anlegen== | ||
+ | <code sql> | ||
+ | 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 | ||
+ | v_driverids | ||
+ | |||
+ | -- mit bind variablen | ||
+ | p_sql varchar2 (200) := ' | ||
+ | p_getid_sql | ||
+ | p_insert_sql | ||
+ | |||
+ | v_count | ||
+ | v_errors | ||
+ | v_message | ||
+ | v_time_start | ||
+ | v_time_stop | ||
+ | |||
+ | v_err_i | ||
+ | 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 | ||
+ | -- 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::' | ||
+ | dbms_output.put_line ('-- Info ::Error rows::' | ||
+ | | ||
+ | -- add a commit each 10000 | ||
+ | | ||
+ | | ||
+ | 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::' | ||
+ | dbms_output.put_line ('-- Error rows::' | ||
+ | |||
+ | for i in 1 .. v_errors | ||
+ | loop | ||
+ | v_err_i := sql%bulk_exceptions (i).error_index; | ||
+ | dbms_output.put_line (' | ||
+ | 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; | ||
+ | |||
+ | 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::' | ||
+ | dbms_output.put_line ('-- Info ::Error rows::' | ||
+ | |||
+ | 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::' | ||
+ | dbms_output.put_line ('-- Error rows::' | ||
+ | |||
+ | for i in 1 .. v_errors | ||
+ | loop | ||
+ | v_err_i := sql%bulk_exceptions (i).error_index; | ||
+ | dbms_output.put_line ( | ||
+ | ' | ||
+ | end loop; | ||
+ | end; | ||
+ | | ||
+ | commit; | ||
+ | | ||
+ | close v_cur; | ||
+ | end if; | ||
+ | |||
+ | v_time_stop := dbms_utility.get_time; | ||
+ | dbms_output.put_line ( ' | ||
+ | || p_testcase | ||
+ | || ' - Zeit:' | ||
+ | || to_char (round ( (v_time_stop - v_time_start) / 100, 2 )) | ||
+ | || ' sec'); | ||
+ | |||
+ | end bulck_load_test; | ||
+ | </ | ||
+ | |||
+ | ===Testlauf=== | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | |||
+ | SQL>exec bulck_load_test(1, | ||
+ | -- Info :: SUCCESS rows ::92160 | ||
+ | -- Info :: Variante: 1 - Zeit:1.33 sec | ||
+ | |||
+ | PL/SQL procedure successfully completed. | ||
+ | |||
+ | SQL>exec bulck_load_test(2, | ||
+ | -- 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, | ||
+ | -- 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:// | ||
+ | * http:// |
prog/plsql_bulk_collect.txt · Zuletzt geändert: 2016/05/20 17:39 von gpipperr