BULK COLLECT

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
Márcia de Paiva Salles
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Ter, 11 Out 2005 6:10 pm
Localização: DF

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
Avatar do usuário
MuLtAnI
Moderador
Moderador
Mensagens: 90
Registrado em: Ter, 01 Jun 2004 9:21 am
Localização: Videira - SC
Contato:

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Ter, 11 Out 2005 6:10 pm
Localização: DF

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Ter, 01 Nov 2005 11:53 pm
Localização: São Paulo
Contato:

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Ter, 11 Out 2005 6:10 pm
Localização: DF

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.
Avatar do usuário
MuLtAnI
Moderador
Moderador
Mensagens: 90
Registrado em: Ter, 01 Jun 2004 9:21 am
Localização: Videira - SC
Contato:

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Ter, 01 Nov 2005 11:53 pm
Localização: São Paulo
Contato:

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Ter, 11 Out 2005 6:10 pm
Localização: DF

Oi Márcio,

As duas tabelas têm uma média de 40.000.000 de registros cada.
Avatar do usuário
leobbg
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 22
Registrado em: Sex, 29 Out 2004 10:25 am
Localização: PORTO ALEGRE - RS
Leo BBG Consultor Oracle

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!! :wink:

Apenas uma dica IMPORTANTE, tome cuidado com o Limit utilizado no Bulk,.. não passe muito desses 50 registros..
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

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;
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 5 visitantes