BULK COLLECT

[Márcia de Paiva Salles]

Oi Pessoal,

Estou fazendo um pl/sql que lê uma tabela temporária e insere em outra, caso o registro já exista, dou update.
Para melhorar a performance, estou usando bulk collect, mas é primeira vez que utilizo este recurso.
Pelos exemplos que tenho, apenas consigo inserir os registros com as linhas guardadas na memória usando FORALL e INSERT/*+APPEND*/. Como faço o update ?

Aguardo a ajuda de vocês.

Márcia

[MuLtAnI]

Oi Márcia, Onde é exatamente sua dúvida? se você consegue efetuar deletes e inserts qual a dificuldade de efetuar um update?

Segue um exemplo de Update com FORALL

Selecionar tudo

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;

Você poderia preencher o Array antes de efetuar o FORALL e mandar o Update apenas nos registros que quisesse.

Isso, ou outra coisa?

espero ter ajudado!

[]'s

[Márcia de Paiva Salles]

Boa tarde MuLtAnI !

Na verdade, foi meio vacilo meu... eu não estava conseguindo fazer o merge, porque estava tentando manipular registro por registro como FORALL...

Já consegui otimizar minha procedure, mas ainda não está tão rápida quanto eu gostaria. Se você tiver um tempinho para analizá-la e sugerir mais alguma alteração.... agradeço de montão !

Selecionar tudo

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, se você estiver na versão 9i ou superior, tenta o MERGE. Então substitua todo o seu código pelo abaixo. E veja se fica mais rápido

Selecionar tudo

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]

Obrigada Márcio !
Estou fazendo esta procedure exatamente para substituir o merge que está muito lento... já fiz o teste com as duas e o bulk é mais rápido.

[MuLtAnI]

O Merge é muito bom em se tratando de DataWarehouse, em casos de inserção e alteração de uma grande quantidade de registros. Acho que não é o caso da Márcia e por isso o bulk é mais eficiente!

[]'s

[mportes]

Márcia, poderia enviar para a lista a quantidade de registros que existe em cada tabela? Índices, etc? Gostaria de reproduzir seu problema - como estudo.
grato.

[Márcia de Paiva Salles]

Oi Márcio,

As duas tabelas têm uma média de 40.000.000 de registros cada.

[leobbg]

segue um exemplo ....

Selecionar tudo

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;

Pelo que já utilizei disso posso dizer que da um super gás em qualquer rotina!!

Apenas uma dica IMPORTANTE, tome cuidado com o Limit utilizado no Bulk,.. não passe muito desses 50 registros..

[dr_gori]

Aí vai um exemplo de BULK DELETE usando ROWID. (apenas para complementar o assunto)

Selecionar tudo

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;