Como trabalhar com grande volume de dados em Oracle?

Você já precisou fazer operações no Oracle com grande volume de dados? Já ouviu comentários do tipo “Estamos com problema de performance”? Ou então “Precisamos fazer tuning”?

Neste post, você vai ver 2 dicas sobre como lidar com grandes volumes de dados em Oracle, usando PL/SQL:

  • merge
  • bulk collect/forall

Você também verá uma terceira dica, que vale para qualquer linguagem de programação, pois está relacionada com a construção da sua lógica.

Vamos aos detalhes de cada uma.

 

Dica 1: Evitar a troca de contexto entre SQL e PL/SQL com uso de merge

O código abaixo está atualizando a tabela tipo_nota_fiscal_importacao de acordo com os dados existentes na tabela tipo_nota_fiscal.
Para cada linha retornada no cursor com informações da tipo_nota_fiscal, é feito um update na tipo_nota_fiscal_importacao. Se o update não atingir nenhum registro da tabela, então é feito um insert.
Assim, ao final do processo, todos os dados da tabela tipo_nota_fiscal foram movidos para a tipo_nota_fiscal_importacao.

for rSistema in (select codigo,
                        descricao
                 from   tipo_nota_fiscal) loop
  --
  update tipo_nota_fiscal_importacao
  set    descricao = rSistema.descricao
  where  codigo    = rSistema.codigo;
  --
  if SQL%ROWCOUNT = 0 then
    --
    insert into tipo_nota_fiscal_importacao (codigo, descricao)
    values (rSistema.codigo, rSistema.descricao);
    --
  end if;
  --
end loop;
Listagem 1 – Exemplo de cursor com update/insert

Se a tabela tipo_nota_fiscal possuir 1000 registros, em um teste interno foi executado em 0,094 seg.

No entanto, esta não é a melhor forma de fazer essa operação, pois apresenta troca de contexto entre as engines SQL e PL/SQL.

FOR e IF são comandos PL/SQL; enquanto SELECT, UPDATE, INSERT são comandos SQL.

Existe uma maneira mais rápida de fazer esta mesma operação sem a troca de contexto, utilizando um único comando SQL:

merge into tipo_nota_fiscal_importacao sis
      using (select codigo, 
                    descricao
             from   tipo_nota_fiscal) imp
      on (sis.codigo = imp.codigo)
      when matched then
        update 
        set sis.descricao = imp.descricao
      when not matched then
        insert (codigo,
                descricao)
        values (imp.codigo,
                imp.descricao);
Listagem 2 – Exemplo de merge

Com os mesmos 1000 registros, o MERGE foi executado em 0,015 seg.

 

Dica 2: Uso de bulk collect e forall

Seguindo a mesma idéia do exemplo anterior, precisamos preencher toda a tabela tipo_nota_fiscal_importacao com dados da tipo_nota_fiscal.

declare 
  --
  cursor cSistema is 
    select codigo, 
           descricao
    from   tipo_nota_fiscal;
  --
begin
  --
  for rSistema in cSistema loop    
    --
    insert into tipo_nota_fiscal_importacao (codigo, descricao)
    values ( rSistema.codigo, rSistema.descricao );
    --
  end loop; 
  --
end;
Listagem 3 – Exemplo de cursor com insert

Para 1000 registros, o tempo de execução foi 0,004 seg. Já no código abaixo podemos fazer esta mesma operação utilizando uma forma diferente de FOR, o FORALL.

Existem 2 conceitos neste código:

  • BULK COLLECT: retorna várias linhas do cursor (neste caso, retorna até 2000 linhas)
  • FORALL: insere / altera várias linhas de uma única vez

Neste caso, 2000 linhas são retornadas do cursor utilizando BULK COLLECT. Essas 2000 linhas são inseridas na nova tabela utilizando o FORALL, e então é realizado um COMMIT.
Em seguida, se ainda houver linhas restantes no cursor, o processo é executado novamente.

declare
  --
  cursor cSistema is 
    select codigo, 
           descricao
    from   tipo_nota_fiscal;
  --
  TYPE tpTbCodigo    IS TABLE OF tipo_nota_fiscal.codigo%type 
                              INDEX BY BINARY_INTEGER;
  TYPE tpTbDescricao IS TABLE OF tipo_nota_fiscal.descricao%type 
                              INDEX BY BINARY_INTEGER;
  --
  vtpTbCodigo    tpTbCodigo;
  vtpTbDescricao tpTbDescricao;
  --
begin
  --
  open cSistema;
  --
  loop
    fetch cSistema bulk collect into vtpTbCodigo, 
                                     vtpTbDescricao limit 2000;
    --
    if vtpTbCodigo.count > 0 then
      --
      forall vnContador in 1 .. vtpTbCodigo.count
        --
        insert into tipo_nota_fiscal_importacao (codigo, descricao)
        values ( vtpTbCodigo(vnContador), vtpTbDescricao(vnContador) );
        --
      commit; -- commit a cada 2.000 registros
      --
    end if;
    --
    exit when cSistema%notfound;
    --
  end loop;
  --
  close cSistema;
  --
end;
Listagem 4 – Exemplo de bulk collect e forall

Para os mesmos 1000 registros, o tempo de execução foi 0,001 seg.

Importante: Estas dicas são válidas para trabalhar com grande quantidade de registros. Em termos de negócio, tipo_nota_fiscal normalmente é uma entidade que possuirá uma quantidade pequena de registros. Foi escolhido este nome de tabela por ser didaticamente mais simples de entender.

Nos exemplos, utilizei um número baixo de registros, mas construções deste tipo são utilizadas para centenas de milhares de registros. O uso de BULK COLLECT e FORALL possuem o commit intermediário exatamente para evitar o estouro do segmento de rollback.

 

Dica 3: Reveja sua lógica

Às vezes, temos um processo lento por conta da lógica utilizada.
Veja o exemplo:

for rNota in (select coluna1,
                     coluna2,
                     valor
              from   nota_fiscal) loop
  --
  if valor > 100 then
    ...
  end if;
  --
end loop;
Listagem 5 – Exemplo de lógica incorreta

Neste caso, é possível alterar a lógica para já diminuir a quantidade de registros retornados no cursor.

for rNota in (select coluna1,
                     coluna2,
                     valor
              from   nota_fiscal
              where  valor > 100) loop
  --
  ...
  --
end loop;
Listagem 6 – Exemplo de lógica correta

Imagine que a tabela nota_fiscal possua 5000 registros e apenas uma nota possui valor maior que R$ 100,00.

Na listagem 5, o SELECT retorna 5000 linhas e internamente ignora as 4999 notas que estão com valor menor que R$ 100,00.

Na listagem 6, o SELECT já retorna apenas 1 nota.

 

Agora você já tem ferramentas para desenvolver códigos sem que eles fiquem lentos. Compare o tempo de cada processo para encontrar a técnica mais apropriada para a sua situação.

Por RONALDO CHICARELI

Arquiteto de software e também apaixonado por novas culturas e idiomas, pois a vida é muito curta pra ser vivida em um só lugar.

Postado em: 17 de agosto de 2015

Confira outros artigos do nosso blog

Nova diretoria de Inovação e Negócios da MATERA busca parcerias

20 de abril de 2017

Vania Hoshii

Páscoa Feliz 2017

18 de abril de 2017

Tamiris Fernanda Cella

Hackathon Internet Banking: UI/UX + APIs

15 de março de 2017

Pedro Farci

Three laws that enable agile software development

09 de março de 2017

Celso Gonçalves Junior

Deixe seu comentário