Como Utilizar Bulk Binds e ForAll para Melhorar a Performance em PL/SQL

PL/SQL BULK BINDS

Bem, pensando sempre no ganho de performace é interessante falar de bunk binds.

Então, a associação de valores a variáveis de pl/sql em comandos de sql é chamado de bind. A associação de uma coleção inteira de uma única vez é chamado de bulk bind.

DECLARE
   TYPE L_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   WCD_L L_NUM;
BEGIN
   FOR X IN 1..2000 LOOP
      WCD_L(X) := X ;
   END LOOP;
   <code>FOR X IN 1..2000 LOOP DELETE FROM TABLE WHERE CAMPO = WCD_L(X);</code>
END;

No exemplo acima o comando Delete é executado 2000 vezes, isto significa que o pl/sql ( Engine ) envia um comando SQL Delete para cada valor da lista.

Cada vez que a pl/sql engine tem necessidade de estabelecer um interrupçao para acionar a SQL Engine, ocorre um overhead.

Sendo assim quando trabalhamos com coleções ( Nested Tables, Index-By, Varray e Host Arrays ) e utilizamos interações ( loops ) usando elementos destas coleções como variáveis Bind ( em comandos SQL), adicionamos um overhead a nossa execução.

Se a interação afetar 5 ou mais linhas do banco de dados, o uso de Bulk Binds pode aumentar a perfomace cosideravelmente.

No link abaixo tempos o mesmo trecho de programa em que substituimos o comando FOR pelo comando ForAll.

DECLARE
   TYPE L_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   WCD_L L_NUM;
BEGIN
   FOR X IN 1..2000 LOOP
      WCD_L(X) := X ;
   END LOOP;
   <code>FORALL X IN 1..2000 LOOP DELETE FROM TABLE WHERE CAMPO = WCD_L(X);</code>
END;

No exemplo a coleção Wcd_L é passada inteira de uma única vez para SQL Engine.

O comando ForAll

Este comando indica à PL/SQL Engine para preparar toda a coleção de entrada ( Bulk Bind ) antes de enviá-la à SQL Engine. A sintaxe é apresentada a seguir.

FOR ALL <INDEX> IN <VALOR INFERIOR>..<VALOR SUPERIOR> <COMANDO SQL>;

Característica / Restrições

O comando SQL presente na sintaxe pode ser Insert, Update ou Delete fazendo referência a elementos da coleção ( a SQL Engine executa um comando Select para cada índice no intervalo )

As fronteiras (<valor inferior> e <valor superior>) devem especificar um intervalo válido de índices numéricos consecutivos ( não precisa ser a coleção inteira, pode ser parte da coleção desde que seja consecutiva).

Todos os elementos da coleção no intervalo especificado devem existir.

O subscrito da coleção não pode ser uma expressão, portanto o texto Where CAMPO = wcd_lista(i+1) é inválido.

Antes de cada comando SQL executado pelo ForAll é criado um SavePoint implícito. Desta forma se no comando ForAll de um determinado programa, a terceira execução( ou utilização do terceiro índice da lista) falha, a primeira e segunda execuções do comando SQL associado não são desmanchadas, apenas a partir do terceiro índice a ação falha.

No próximo post irei falar sobre cláusulas Bulk Collect. Bons estudos !!!