Cade o registro que estava aqui? ou Que mal pode fazer pedir para o Oracle analisar uma tabela?

Outro dia um amigo meu foi envolvido num processo que começou a dar problema após uma pequena alteração de melhoria de performance.

Após um belo tempo tentando entender o que estava acontecendo ele resolveu me mostrar o código para tentar desvendar o mistério.

Modelagem de teste bem resumida:

SQL>
SQL> desc tst_temp;
Name                           Null?    Type
------------------------------ -------- ------
N                                       NUMBER

SQL> desc tst_fisica;
Name                           Null?    Type
------------------------------ -------- ------
N                              NOT NULL NUMBER

SQL>

Simplificação do que o processo fazia:

SQL>
SQL> DECLARE
2   n NUMBER;
3  BEGIN
4   --
5   INSERT INTO tst_temp VALUES(1);
6   --
7   INSERT INTO tst_fisica VALUES(1);
8   --
9   EXECUTE IMMEDIATE 'ANALYZE TABLE tst_fisica COMPUTE STATISTICS';
10   --
11   SELECT COUNT(1) qtd_temp
12   INTO   n
13   FROM   tst_temp t1
14   JOIN   tst_fisica t2
15   ON     t1.n = t2.n;
16   --
17   dbms_output.put_line('Encontrados ' || n || ' registro(s)');
18   --
19  END;
20  /
Encontrados 0 registro(s)

PL/SQL procedure successfully completed.

SQL>

Veja que a mensagem nos diz: Encontrados 0 registro(s)

A pergunta era: Onde foi para o registro que acabei de inserir?

Essa é a pergunta que eu te faço, meu caro leitor.

Tente descobrir o que ocorre neste processo.

Se não tiver a menor ideia, vou dar uma ajudinha.

Seguem as instruções de criação das tabelas usadas nos testes:

SQL>
SQL> CREATE GLOBAL TEMPORARY TABLE tst_temp(n NUMBER);

Table created.

SQL> CREATE TABLE tst_fisica(n NUMBER PRIMARY KEY);

Table created.

SQL>

Vou começar a explicar o que ocorreu, então, se não descobriu o problema, mas ainda quer tentar, pare de ler.

O processo falha por causa da combinação:
– TABELA TEMPORÁRIA POR TRANSAÇÃO
– INSTRUÇÃO DDL

No Oracle, por default, uma tabela global temporary é criada com a característica “ON COMMIT DELETE ROWS”, que faz com que ela seja esvaziada toda vez que uma transação for finalizada, com um commit por exemplo.

Caso não seja esse o comportamento desejado, é necessário complementar a sua instrução de criação com a cláusula “ON COMMIT PRESERVE ROWS”, neste caso a tabela temporária se esvazia automaticamente a cada nova sessão.

Quando apontei esse detalhe fui logo indagado: “mas no meu processo não tem COMMIT”.
Realmente não há commit explícito no código, mas é uma característica do Oracle que toda instrução DDL comita a transação pendente.

No nosso caso a a instrução DDL é justamente a alteração que prometia melhoria de performance:
“ANALYZE TABLE tst_fisica COMPUTE STATISTICS”

Caso estejam pensando numa maneira de contornar o problema, já vou logo avisando que não adianta substituir o ANALYZE pelo dbms_stats:

SQL>
SQL> DECLARE
2   n NUMBER;
3  BEGIN
4   --
5   INSERT INTO tst_temp VALUES(2);
6   --
7   INSERT INTO tst_fisica VALUES(2);
8   --
9   dbms_stats.gather_table_stats(USER,'TST_FISICA');
10   --
11   SELECT COUNT(1) qtd_temp
12   INTO   n
13   FROM   tst_temp t1
14   JOIN   tst_fisica t2
15   ON     t1.n = t2.n;
16   --
17   dbms_output.put_line('Encontrados ' || n || ' registro(s)');
18   --
19  END;
20  /
Encontrados 0 registro(s)

PL/SQL procedure successfully completed.

SQL>

Vejam que a API também finaliza a transação.

Na ocasião, perguntei ao meu colega (que não trabalha na mesma empresa que eu, nem nos clientes que eu atendo) qual era o prazo que ele tinha para publicar a alteração de performance que ele estava fazendo.

Ele me respondeu que já tinha publicado em ambiente de produção, mas que não sabia que a inclusão desta linha (ANALYZE) podia dar esse tipo de problema, afinal “que mal pode fazer pedir para o Oracle analisar uma tabela?”

Para ele foi um aprendizado e tanto, mas pra quem ainda não pegou, fica o recado:
1 – Antes de incluir chamadas puco ortodoxas no código, sempre converse com DBAs e outros desenvolvedores.
2 – Testem, testem, testem

A questão da performance em si, eu gostaria de deixar para um próximo post, então, por hoje é só.

SQL> DROP TABLE tst_temp;

Table dropped.

SQL> DROP TABLE tst_fisica;

Table dropped.

SQL> exit