/home/ericogr/blog

Experiências no desenvolvimento de aplicações e afins

Utilizando Bulk Collect no Oracle

Posted by ericogr em 15 maio 2009

Uma forma eficiente para manipularmos grandes quantidades de dados pode ser a utilização de BULK COLLECT. Se utilizarmos corretamente, podemos ter um grande ganho no desempenho das aplicações, pois os dados manipulados pelas coleções ficam armazenados diretamente na memória. Por outro lado, podemos ter problemas se os limites para a quantidade de dados carregados em memória não for dimensionado corretamente. Para cada sessão de usuário, o Oracle alocará espaço em uma área de memória chamada PGA (Program Global Area). Se tivermos 100 usuários conectados simultaneamente utilizando os recursos uma coleção que ocupa 10Mb em memória, teremos um consumo total de 1Gb!

Felizmente podemos limitar os dados que são armazenados nas coleções usando a clausula LIMIT. Desta forma, podemos analisar a relação custo/benefício concedendo limites maiores ou menores para nossa coleção.

É importante lembrar que do Oracle Database 10g em diante os cursores (FOR LOOPS) são automaticamente otimizados para velocidades próximas as do BULK COLLECT.

Vamos ver na prática como tudo isso funciona.

Criar tabela teste:

create table tab19x (
id number(5),
nome varchar2(30),
constraint tab19x_pk primary key (id));

Popular a tabela:

begin
insert into tab19x
select level, dbms_random.string(‘a’, 30) from dual connect by level <= 123;
commit;
end;

Abaixo temos um bloco de código PL/SQL anônimo, onde temos um BULK COLLECT limitando a coleção para 50 registros. Isso significa que cada vez que os dados são lidos do cursor, no máximo 50 registros por vez são alocados na memória. Toda vez que a coleção for carregada, uma mesma quantidade de memória será alocada na PGA, mesmo que a tabela aumente de tamanho em número de linhas.

Utilizando o BULK COLLECT

declare
cursor cur_tab19x is
select id, nome
from tab19x;

–Abaixo crio o tipo para a coleção
type trow_tab19x is table of cur_tab19x%rowtype
index by pls_integer;

–Declaro a coleção row_tab19x
row_tab19x trow_tab19x;
begin
open cur_tab19x;
loop
fetch cur_tab19x BULK COLLECT into row_tab19x limit 50;

–NÃO use: exit when cur_tab19x%notfound;
exit when row_tab19x.count = 0;

for i in 1..row_tab19x.count
loop
dbms_output.put_line(‘Cod: ‘ || row_tab19x(i).id || ‘ valor: ‘ || row_tab19x(i).nome);
end loop;
end loop;
close cur_tab19x;
end;

Quando utilizamos BULK COLLECT precisamos lembrar que:

  • As coleções são preenchidas sequencialmente, iniciando do índice valor 1;
  • Interagir com a coleção de 1 até coleção.COUNT;
  • A coleção é vazia quando não tivermos mais linhas depois de um fetch;
  • Sempre utilize o método coleção.COUNT para ver se existem mais linhas para serem processadas;
  • Ignore os valores retornados pelos atributos do cursor, especialmente o cursor%NOTFOUND;

Fonte: http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html

Uma resposta to “Utilizando Bulk Collect no Oracle”

  1. Anderson Souza said

    Por que não usar…
    NÃO use: exit when cur_tab19x%notfound???

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

 
%d blogueiros gostam disto: