BULK COLLECT

[Márcia de Paiva Salles]

Hi everyone,

I'm doing a PL / SQL that reads a temporary table and inserts in another if the record already exists, I give Update.
To improve performance, I'm using Bulk Collect, but it's the first time I use this feature.
For the examples I have, I can only insert the records with the rows stored in memory using forall and insert / * + append * /. How do I update?

I await your help.

Márcia

[MuLtAnI]

Hi Márcia, where is your doubt exactly? If you can make deletes and inserts what difficulty of performing an update?

follows an example of update with forall

Select all

 
DECLARE 
TYPE NumList IS VARRAY(10) OF NUMBER; 
depts NumList := NumList(20,30,50,55,57,60,70,75,90,92); 
BEGIN 
FORALL j IN 4..7 -- bulk-bind only part of varray 
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j); 
END; 

You could fill the array before you make the forall and send the update only in the records that He wanted to.

, or another thing?

I hope I have helped!

[] 's

[Márcia de Paiva Salles]

Good afternoon Multani!

In fact, it was kind of hesitation of mine ... I was not able to do the merge because I was trying to manipulate registry by registration as forall ...

I'vê got to optimize my procedure, but it's not as fast as I'd like. If you have a little time to analyze it and suggest any more change .... Thank you for heap!

Select all

DECLARE 
bulk_errors   EXCEPTION; 
PRAGMA EXCEPTION_INIT (bulk_errors,  -24381); 
TYPE nmb_varray IS VARRAY (50000000) OF NUMBER(40); 
TYPE dts_varray IS VARRAY (50000000) OF DATE; 
TYPE chr_varray IS VARRAY (50000000) OF VARCHAR2(255); 
V_account_key   chr_varray; 
V_account_type  nmb_varray; 
V_text1         chr_varray;  
V_text2         chr_varray;  
V_text3         chr_varray; 
V_text4         chr_varray; 
V_text5         chr_varray;  
V_text6         chr_varray; 
CURSOR c_Actype IS (SELECT account_key, 
                           account_type,  
                           text1,  
                           text2,  
                           text3, 
                           text4, 
                           text5,  
                           text6  
                     FROM cs_actype_map_tmp); 
Reg C_Actype%ROWTYPE; 
v_contador    number; 
v_quantReg    number; 
v_indice      number; 
BEGIN 
v_contador    := 0; 
v_quantReg    := 0; 
v_indice      := 0; 
OPEN C_Actype; 
LOOP 
  FETCH C_Actype BULK COLLECT INTO 
    V_account_key, v_account_type, V_text1, 
    V_text2, V_text3, V_text4, V_text5,V_text6 
  LIMIT 10000; 
  BEGIN 
    FORALL a IN 1..V_account_key.count  
          UPDATE cs_actype_map_RENAME  
          SET account_type  = v_account_type(a),  
            text1         = v_text1(a),  
            text2         = v_text2(a),  
            text3         = v_text3(a),  
            text4         = v_text4(a),  
            text5         = v_text5(a),   
            text6         = v_text6(a)  
          WHERE account_key = v_account_key(a); 
 
  
      IF SQL%ROWCOUNT = 0 THEN 
        FORALL a IN 1..V_account_key.count  
          INSERT/*+APPEND*/ INTO   cs_actype_map_RENAME 
          (account_key, account_type, text1, text2, text3, text4, text5, text6 )  
          VALUES  
          (v_account_key(a), v_account_type(a), v_text1(a), 
           v_text2(a), v_text3(a), v_text4(a), v_text5(a), v_text6(a)); 
      ELSE  
        IF SQL%ROWCOUNT <> V_account_key.count THEN 
        FOR a IN 1..V_account_key.count 
        LOOP 
          IF SQL%BULK_ROWCOUNT(a) = 0 THEN 
            INSERT/*+APPEND*/ INTO   cs_actype_map_RENAME 
            (account_key, account_type, text1, text2, text3, text4, text5, text6 )  
            VALUES  
            (v_account_key(a), v_account_type(a), v_text1(a), 
             v_text2(a), v_text3(a), v_text4(a), v_text5(a), v_text6(a));  
            DBMS_OUTPUT.PUT_LINE ('INSERI LINHA'||v_account_key(a)); 
          END IF; 
        END LOOP; 
        END IF; 
      END IF; 
  
   COMMIT; 
  END; 
  EXIT WHEN C_Actype%NOTFOUND; 
END LOOP; 
COMMIT; 
CLOSE C_Actype; 
END; 

[mportes]

Márcia, if you are in version 9i or higher, try the merge. Then replace your entire code from below. And see if it gets faster;)

Select all

 
begin 
   merge into cs_actype_map_RENAME r 
   using cs_actype_map_tmp t 
      on (r.account_key = t.account_key) 
    when matched then 
         update r.account_type  = t.account_type, 
                r.text1         = t.text1, 
                r.text2         = t.text2, 
                r.text3         = t.text3, 
                r.text4         = t.text4, 
                r.text5         = t.text5,  
                r.text6         = t.text6  
    when not matched then 
         insert (r.account_key, r.account_type, r.text1, r.text2, r.text3,  
                 r.text4, r.text5, r.text6 ) 
         values (t.account_key, t.account_type, t.text1, t.text2, t.text3,  
                 t.text4, t.text5, t.text6 ); 
end; 
/ 

[Márcia de Paiva Salles]

Thank you Márcio!
I'm doing this proceeding exactly to replace the merge that is too slow ... I already did the test with both and the bulk is faster.

[MuLtAnI]

Merge is very good at dealing with DataWarehouse, in cases of insertion and change from a large number of records. I think it's not the case of Márcia and so the bulk is more efficient!

[] 's

[mportes]

Márcia could send to the list the amount of records that exist on each table? Indexes, etc.? I would like to reproduce your problem - as a study.
grateful.

[Márcia de Paiva Salles]

Hi Márcio,

The two tables have an average of 40,000,000 records each.

[leobbg]

Here is an example ....

Select all

 
 
declare 
  -- 
  type pl_tab is table of varchar2(100) index by binary_integer; 
  m_field pl_tab; 
  -- 
  m_field_01 pl_tab; 
  m_field_02 pl_tab; 
  m_field_03 pl_tab; 
  m_field_04 pl_tab; 
  m_field_05 pl_tab; 
  m_field_06 pl_tab; 
  m_field_07 pl_tab; 
  -- 
  cursor c1 is 
    select coluna_1 
    ,      coluna_2 
    ,      coluna_3 
    ,      coluna_4 
    ,      coluna_5 
    ,      coluna_6 
    ,      coluna_7 
    from   tabela_1; 
  -- 
begin 
  -- 
  open  c1; 
  loop 
    -- 
    fetch c1 bulk collect into  
             m_field_01, m_field_02, m_field_03 
           , m_field_04, m_field_05, m_field_06 
           , m_field_07  limit 50; 
    --      
    forall i in m_field_01.first..m_field_01.last 
    merge into Tabela_de_destino fat 
    using ( select m_field_01(i) cod_estabelecimento 
            ,      m_field_02(i) cod_data_venda 
            ,      m_field_03(i) nr_seq_d 
            ,      m_field_04(i) nr_seq_i 
            ,      m_field_05(i) cod_data_remessa 
            ,      m_field_06(i) quantidade_remessa 
            ,      m_field_07(i) valor_remessa 
            from   dual ) mer 
        on ( fat.cod_estabelecimento = mer.cod_estabelecimento and 
             fat.cod_data_venda      = mer.cod_data_venda      and 
             fat.nr_seq_d            = mer.nr_seq_d            and 
             fat.nr_seq_i            = mer.nr_seq_i ) 
      when matched then  
                 update  
                 set fat.cod_data_remessa   = mer.cod_data_remessa 
                 ,   fat.quantidade_remessa = mer.quantidade_remessa 
      when not matched then 
                 insert 
                   ( fat.cod_estabelecimento, fat.cod_data_venda 
                   , fat.nr_seq_d           , fat.nr_seq_i 
                   , fat.cod_data_remessa   , fat.quantidade_remessa 
                   , fat.valor_remessa ) 
                 values 
                   ( mer.cod_estabelecimento , mer.cod_data_venda 
                   , mer.nr_seq_d            , mer.nr_seq_i 
                   , mer.cod_data_remessa    , mer.quantidade_remessa 
                   , mer.valor_remessa ); 
      -- 
      limpa_pl; 
      commit; 
      -- 
      exit when c1%notfound; 
      -- 
    end loop; 
    -- 
    close c1; 
    -- 
  end; 
 
procedure limpa_pl is 
begin 
  -- 
    m_field_01.delete; m_field_02.delete; m_field_03.delete; 
    m_field_04.delete; m_field_05.delete; m_field_06.delete; 
    m_field_07.delete; 
  -- 
end; 
 

From what I already used this I can say that a super gas in any routine !! : Wink:

Just an important tip, be careful with the limit used in the bulk, .. do not spend much of these 50 records ..

[dr_gori]

There is an example of Bulk Delete using Rowid. (only to complement the matter)

Select all

DECLARE 
 cursor get_posicoes  
 is 
 select rowid 
 from TABELA ori 
      where ori.dt_time_position < (sysdate - 2) 
        and ori.cd_rastreador in 
          ( 
            select rast.cd_rastreador 
            from 
              tb_veiculo    veic 
            , tb_rastreador rast 
            where veic.cd_rastreador = rast.cd_rastreador 
              and veic.cd_entidade   = 75  
          ); 
 
  type trowid IS TABLE OF rowid; 
  tab_rowid trowid; 
  vrow_count number := 0; 
 
BEGIN 
 
  open get_posicoes; 
  loop 
    fetch get_posicoes bulk collect into tab_rowid limit 500; 
    exit when get_posicoes%notfound; 
 
    forall j in 1..500 
      delete from TABELA ori 
      where rowid = tab_rowid(j); 
 
      vrow_count := vrow_count + sql%rowcount; 
      commit; 
  end loop; 
 
  close get_posicoes; 
  dbms_output.put_line(vrow_count||' registros removidos com sucesso!'); 
 
exception 
  when others then 
    dbms_output.put_line(vrow_count||' registros removidos com sucesso!'); 
    raise_application_error(-20000,'Erro ao remover registros da tabela'); 
 
END;