Melhore a performance de inclusão com os hints APPEND e APPEND_VALUES

Hints Append e Append_Values

O banco de dados trabalha “tapando buracos”. Ou seja, sempre tenta utilizar-se dos espaços disponíveis de exclusões de registros passados de uma tabela, realimentando estes endereços com um novo dado inserido. Ao analisarmos a sangue frio vê-se que isto tem um custo, haja vista da necessidade de ao longo da tabela consultar os “espaços em brancos” para promover a gravação da nova informação.

Os hints APPEND e APPEND_VALUES mudam a cara do processo, fazendo com que os novos registros sejam colocados ao final da tabela, eliminando o custo de verificação de espaços disponíveis intermediários. Neste sentido, promove uma melhora na performance de inclusão, pois já sabe que os novos dados serão alocados no final da tabela, ignorando demais verificações. O hint APPEND é utilizado para INSERT INTO … SELECT, enquanto APPEND_VALUES para quando o INSERT INTO possui a clausula VALUES ao invés do SELECT.

Abaixo foram criados scripts para comparação de performance. Inicialmente são criadas duas tabelas: TEMP_TESTE e TEMP_TESTE_AUX. A primeira servirá como tabela de simulação, onde será alimentada com 50 mil registros e logo em seguida, através da utilização do SAMPLE, terá 15% dos seus dados deletados, gerando espaços disponíveis ao longo da tabela e de forma aleatória. A segunda tabela terá 30 mil linhas que serão utilizadas para promover o INSERT na tabela TEMP_TESTE.

Primeiramente, criaremos a tabela TEMP_TESTE:

CREATE TABLE temp_teste
  (my_id NUMBER, numero NUMBER
  );

Em seguida cria-se a tabela TEMP_TEST_AUX com 30 mil registros:

create table temp_teste_aux as
SELECT ROWNUM my_id,
  ROUND(dbms_random.VALUE(1,7000)) numero
FROM dual
CONNECT BY LEVEL <= 30000; --quantidade de registro a serem criados

Os dois exemplos abaixo tem o mesmo propósito, incluir 30 mil registros na tabela TEMP_TESTE, contudo, primeiro utilizando INSERT INTO SELECT e em seguida utilizando INSERT INTO VALUES com seus respectivos Hints.

Executaremos a comparação de desempenho, neste primeiro exemplo, utilizando o Hint APPEND:

set SERVEROUTPUT ON

DECLARE
  v_time_i_one NUMBER;
  v_time_i_two NUMBER;
  v_time_f_one NUMBER;
  v_time_f_two NUMBER;

PROCEDURE montatabela
IS
type trow_type
IS
  TABLE OF temp_teste%rowtype INDEX BY pls_integer;
  trow trow_type;

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_teste';

  INSERT INTO temp_teste
  SELECT ROWNUM my_id,
    ROUND(dbms_random.VALUE(1,7000)) numero
  FROM dual
    CONNECT BY LEVEL <= 50000; -- quantidade de registro a serem criados

  COMMIT;

  SELECT my_id, numero bulk collect INTO trow FROM temp_teste sample(15);
  FORALL nX IN 1 .. trow.count
  DELETE temp_teste WHERE my_id = trow(nX).my_id;
  COMMIT;

END montatabela;

BEGIN
  montatabela;
  v_time_i_one := dbms_utility.get_time();
  INSERT /*+ APPEND */ INTO temp_teste select * from temp_teste_aux;
  v_time_f_one := (dbms_utility.get_time - v_time_i_one) / 100;
  dbms_output.put_line('Atualizacao COM Hint APPEND: ' || v_time_f_one || ' segundos.');
  ROLLBACK;
  montatabela;
  v_time_i_two := dbms_utility.get_time();
  INSERT INTO temp_teste select * from temp_teste_aux;  
  v_time_f_two := (dbms_utility.get_time - v_time_i_two) / 100;
  ROLLBACK;
  dbms_output.put_line('Atualizacao SEM Hint APPEND: ' || v_time_f_two || ' segundos.');
  dbms_output.put_line('Ou seja, ' || ROUND((v_time_f_two*100)/v_time_f_one,2) || '% mais rapido.');

END;

Agora a comparação de performance com a utilização de Hint APPEND_VALUES

SET serveroutput ON

DECLARE
  v_time_i_one NUMBER;
  v_time_i_two NUMBER;
  v_time_f_one NUMBER;
  v_time_f_two NUMBER;
  TYPE taux_type IS TABLE OF temp_teste_aux%rowtype INDEX BY pls_integer;
  taux taux_type;

PROCEDURE montatabela
IS
TYPE trow_type
IS
  TABLE OF temp_teste%rowtype INDEX BY pls_integer;
  trow trow_type;

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_teste';
  INSERT INTO temp_teste
  SELECT ROWNUM my_id,
    round(dbms_random.VALUE(1,7000)) numero
  FROM dual
    CONNECT BY LEVEL <= 50000; -- quantidade de registro a serem criados

  COMMIT;

  SELECT my_id, numero BULK COLLECT INTO trow FROM temp_teste SAMPLE(15);
  forall nx IN 1 .. trow.count
  DELETE temp_teste WHERE my_id = trow(nx).my_id;

  COMMIT;

END montatabela;

BEGIN
  montatabela;

  SELECT my_id,
    numero BULK COLLECT
  INTO taux
  FROM temp_teste_aux;  
  v_time_i_one := dbms_utility.get_time();
  forall nx IN 1 .. taux.count
  INSERT /*+ APPEND_VALUES */ INTO temp_teste (my_id, numero) VALUES (taux(nx).my_id,taux(nx).numero);
  v_time_f_one := (dbms_utility.get_time - v_time_i_one) / 100;
  dbms_output.put_line('Atualizacao COM Hint APPEND: ' || v_time_f_one || ' segundos.');
  ROLLBACK;

  montatabela;

  v_time_i_two := dbms_utility.get_time();
  forall nx IN 1 .. taux.count
  INSERT INTO temp_teste (my_id, numero) VALUES (taux(nx).my_id,taux(nx).numero);
  v_time_f_two := (dbms_utility.get_time - v_time_i_two) / 100;
  ROLLBACK;
  dbms_output.put_line('Atualizacao SEM Hint APPEND: ' || v_time_f_two || ' segundos.');
  dbms_output.put_line('Ou seja, ' || round((v_time_f_two*100)/v_time_f_one,2) || '% mais rapido.');

END;

Pontos de bastante atenção na utilização dos Hint APPEND e APPEND_VALUES:

1. As CONSTRAINTS de INTEGRIDADE REFERENCIAL não são asseguradas;
2. Os TRIGGERS não são disparados;
3. APPEND_VALUES apenas está disponivel no Oracle 11 R2.

Ao analisar os dois primeiros pontos vê-se a possibilidade de corrupção dos dados a nível lógico de integridade referencial. Em situações onde existem integridades referenciais ou trigger, o Oracle, provavelmente, ignorará os hints para garantir a consistências das informações.

Ainda pode-se deparar com o erro na utilização de LOOP, FOR ou WHILE sem o commit de linha a linha. De qualquer maneira, para um melhor ganho, sempre tente combinar com FORALL.

Referências

• https://oracle-base.com/articles/11g/append-values-hint-11gr2