Atribuição em CLOB no Oracle

Defronte a necessidade de utilizar um CLOB para processamento de informações no Oracle 10g e 11g sob a forma de concatenação de múltiplas pequenas porções de texto, enfrentamos um problema de custo de processamento. Mas há algumas abordagens que oferecem uma grande melhoria nos tempos de execução.

Este artigo é um repost do artigo de Martin D'Souza.

Martin faz um benchmark com abordagens extremamente simples, compondo concatenação dos tipos CLOB e VARCHAR2 e expressando resultados muito interessantes.

Houveram 5 (cinco) abordagens:

• O procedimento mais intuitivo para concatenação:  CLOB := CLOB || VARCHAR
• Fazer um cast simples da porção do loop para o tipo CLOB com objetivo de forçar uma concatenação entre mesmo tipo: CLOB := CLOB || TO_CLOB(VARCHAR2)
• Criação de um CLOB temporário para realizar a formatação e da porção do loop e na próxima linha realizar a concatenação do CLOB final: CLOB := CLOB || TMP_CLOB
• Preencher um VARCHAR2 até que haja sinalização de overflow para então adicionar o conteudo no CLOB: APP(CLOB, TMP_VARCHAR2, VARCHAR2)

Foi adicionado posteriormente, por um colaborador anônimo ao post do Martin, o procedimento que mais assusta do ponto de vista de programação PL/SQL quando comparado com o resultado obtido:

• Utilizar a package DBMS_LOB para o tratamento de concatenação atavés do método append: DBMS_LOB.append

Adicionei uma abordagem para prover futuramente um estudo sobre maximização de buffer:

• Concatenar um VARCHAR2 com porções controladas para periodicamente repassar para o CLOB: CLOB := MOD(VARCHAR2)

Realizei bateria de testes em um Oracle 10g Release 2 instalado em uma máquina virtual Linux CentOS 6.4 com 1GB de memória, que apresentou o seguinte resultado:

1.000 Iterações
CLOB := CLOB || VARCHAR2 method...........: +000000000 00:00:00.120953000
CLOB := CLOB || TO_CLOB(VARCHAR2) method..: +000000000 00:00:00.064219000
CLOB := CLOB || TMP_CLOB method...........: +000000000 00:00:00.067526000
APP(CLOB, TMP_VARCHAR2, VARCHAR2) method..: +000000000 00:00:00.007076000
DBMS_LOB.append method....................: +000000000 00:00:00.034099000
CLOB: CLOB := MOD(VARCHAR2) method........: +000000000 00:00:00.005993000
 
10.000 Iterações
CLOB := CLOB || VARCHAR2 method...........: +000000000 00:00:05.063880000
CLOB := CLOB || TO_CLOB(VARCHAR2) method..: +000000000 00:00:02.706519000
CLOB := CLOB || TMP_CLOB method...........: +000000000 00:00:02.722718000
APP(CLOB, TMP_VARCHAR2, VARCHAR2) method..: +000000000 00:00:00.073734000
DBMS_LOB.append method....................: +000000000 00:00:00.409170000
CLOB: CLOB := MOD(VARCHAR2) method........: +000000000 00:00:00.071165000
 
50.000 Iterações
CLOB := CLOB || VARCHAR2 method...........: +000000000 00:01:58.840395000
CLOB := CLOB || TO_CLOB(VARCHAR2) method..: +000000000 00:01:02.119731000
CLOB := CLOB || TMP_CLOB method...........: +000000000 00:01:02.911362000
APP(CLOB, TMP_VARCHAR2, VARCHAR2) method..: +000000000 00:00:00.369891000
DBMS_LOB.append method....................: +000000000 00:00:02.125929000
CLOB: CLOB := MOD(VARCHAR2) method........: +000000000 00:00:00.681756000
 
100.000 Iterações
CLOB := CLOB || VARCHAR2 method...........: +000000000 00:08:16.762152000
CLOB := CLOB || TO_CLOB(VARCHAR2) method..: +000000000 00:04:15.652196000
CLOB := CLOB || TMP_CLOB method...........: +000000000 00:04:16.725465000
APP(CLOB, TMP_VARCHAR2, VARCHAR2) method..: +000000000 00:00:00.790991000
DBMS_LOB.append method....................: +000000000 00:00:04.237341000
CLOB: CLOB := MOD(VARCHAR2) method........: +000000000 00:00:02.658465000

Os resultados demonstram que as abordagens de concatenação em um buffer secundário do tipo VARCHAR2 para então prosseguir a concatenação para um CLOB tem as melhores respostas.

O script atualizado está abaixo:

DECLARE
  v_start TIMESTAMP;
  v_end TIMESTAMP;
  v_clob CLOB;
  v_tmp_clob CLOB;
  v_iterations PLS_INTEGER := 50000; -- Used 1,000, 10,000, and 100,000 for testing
 
  v_vc VARCHAR2(32000);
  v_len PLS_INTEGER := 0;
 
  PROCEDURE APP(v_clob IN OUT NOCOPY CLOB, v_vc IN OUT NOCOPY VARCHAR2, v_app VARCHAR2) IS
  BEGIN
    v_vc := v_vc || v_app;
  EXCEPTION  WHEN VALUE_ERROR THEN
    IF v_clob IS NULL THEN
      v_clob := v_vc;
    ELSE
      DBMS_LOB.APPEND(v_clob, v_vc);
      v_vc := v_app;
    END IF;
  END;
 
BEGIN
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
 
  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CHAR (SYSTIMESTAMP) || ', ';
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || VARCHAR2 method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
 
  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CLOB (TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TO_CLOB(VARCHAR2) method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
 
  FOR i IN 1 .. v_iterations LOOP
    v_tmp_clob := TO_CHAR (SYSTIMESTAMP) || ', ';
    v_clob := v_clob || v_tmp_clob;
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TMP_CLOB method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
  v_clob := 'h'; -- need to initialize it;
 
  FOR i IN 1 .. v_iterations LOOP
    APP(v_clob, v_vc, TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;
  v_clob := v_clob || v_vc;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('APP(CLOB, TMP_VARCHAR2, VARCHAR2) method: ' || TO_CHAR (v_end - v_start));
 
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
  v_clob := 'h'; -- need to initialize it;
 
  FOR i IN 1 .. v_iterations LOOP
    DBMS_LOB.append (v_clob, TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('DBMS_LOB.append method: ' || TO_CHAR (v_end - v_start));
 
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
  v_clob := 'h'; -- need to initialize it;
 
  FOR i IN 1 .. v_iterations LOOP
    IF MOD(i,150) = 1 THEN
        v_clob := v_clob || v_vc;
        v_vc := NULL;
    END IF;
    v_vc := v_vc || TO_CHAR (SYSTIMESTAMP) || ', ';
  END LOOP;
 
  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB: CLOB := MOD(VARCHAR2) method: ' || TO_CHAR (v_end - v_start));
END;