Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_bulk_collect

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:plsql_bulk_collect [2016/05/20 17:37]
gpipperr [Schreiben der Daten mit einem FORALL]
prog:plsql_bulk_collect [2016/05/20 17:39] (aktuell)
gpipperr [Ein Demo]
Zeile 1: Zeile 1:
 +=====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==
 +<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     driver_id;
 +
 +..
 +open v_cur for p_alternativ_getid_sql using p_key;
 + fetch v_cur bulk collect into v_driverids
 +close v_cur;
 +
 +</code>
 +
 +
 +==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;
 +   
 +</code>
 +
 +  * 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  <fc #4682b4>**EXIT WHEN v_driverids.COUNT = 0;**</fc> 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:
 +<code sql>
 +forall i in 1 .. v_driverids.COUNT
 +    execute immediate p_alternativ_insert_sql using v_driverids (i);
 +</code>
 +
 +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.
 +
 +<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  := 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;
 +
 +</code>
 +
 +
 +----
 +
 +
 +
 +=== 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
 +   insert into EMPLOYEES2  select * from EMPLOYEES2;
 +    commit;
 + end loop;
 +end;
 +/
 +
 +</code>
 +
 +==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             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;
 +</code>
 +
 +===Testlauf===
 +
 +
 +<code sql>
 +
 +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.
 +</code>
 +
 +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
"Autor: Gunther Pipperr"
prog/plsql_bulk_collect.txt · Zuletzt geändert: 2016/05/20 17:39 von gpipperr