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
  

Poston Tue, 25 Oct 2005 1:32 pm

Hi guys, I'm doing a pl/sql statement that reads a temporary table and inserts them into another, if the record already exists, give update.
To improve performance, I am using bulk collect, but is first time I use this feature.
The examples that I have, I can only insert the rows with the rows stored in memory using FORALL and INSERT/* + APPEND */. How do I update?

I await your help.

Márcia
Márcia de Paiva Salles
Location: DF

Poston Thu, 27 Oct 2005 12:54 pm

Hi Marcia, where exactly is your question? If u can make deletes and inserts which the difficulty to make an update?

The following is an example of Update with FORALL
Code: 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 populate the Array before performing the FORALL and send the Update just the records you want.

That, or something else?

I hope I helped!

[] 's
MuLtAnI
Location: Videira - SC


Poston Thu, 27 Oct 2005 2:51 pm

Good afternoon MuLtAnI.

Actually, it was my mistake ... I wasn't able to do the merge, because I was trying to handle record by record as FORALL ...

I'vê managed to optimize my procedure, but still not as fast as I'd like. If you have the time to run it and suggest any changes .... I appreciate a lot!

Code: 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;
Márcia de Paiva Salles
Location: DF

Poston Wed, 02 Nov 2005 12:55 am

Marcia, if you are on version 9i or higher, try the MERGE. Then replace all the code below. And see if it gets faster;)
Code: 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;
/
mportes
Location: São Paulo


Poston Thu, 03 Nov 2005 8:24 am

Thanks Márcio!
I'm doing this procedure exactly to replace the merge that is very slow ... I'vê done the test with both and the bulk is faster.
Márcia de Paiva Salles
Location: DF

Poston Thu, 03 Nov 2005 8:55 am

The Merge is very good when it comes to DataWarehouse, in cases of insertion and modification of a large amount of records. I don't think this is the case of Marcia and the bulk is more efficient!

[] 's
MuLtAnI
Location: Videira - SC


Poston Fri, 04 Nov 2005 7:44 am

Marcia, could send to list the amount of records that exist in each table? Indexes, etc? I would like to reproduce your problem-how to study.
grateful.
mportes
Location: São Paulo


Poston Fri, 04 Nov 2005 8:37 am

Hi Marcio, both tables have an average of 40,000,000 records each.
Márcia de Paiva Salles
Location: DF

Poston Thu, 08 Dec 2005 10:17 am

the following is an example ....

Code: 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'vê used it I can say that from a super gas in any routine!! : wink: Just an important tip, be careful with the Limit used in Bulk, ... don't spend too much of these 50 records
leobbg
Location: PORTO ALEGRE - RS

Leo BBG Consultor Oracle

Poston Wed, 16 Aug 2006 5:08 pm

Here's an example of a BULK DELETE using ROWID. (only to complement the subject)

Code: 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;
dr_gori
Location: Portland, OR USA

Thomas F. G

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


  • See also
    Replies
    Views
    Last Post


        Return to PL/SQL

        Who is online

        Users browsing this forum: No registered users and 6 guests