Utilização de tabelas temporárias no Oracle

Um recurso muito interessante do Oracle e que muitas vezes os programadores não utilizam (talvez por não terem conhecimento) são as Tabelas Temporárias.

Basicamente, uma tabela temporária é muito parecida com uma tabela que criamos normalmente com o comando CREATE TABLE, nós podemos fazer as operações DML (Insert, Update e Delete), podemos utilizá-la em consultas, podemos ter índices e triggers e podemos chamá-la por meio de um algoritmo PL/SQL.

O diferencial de uma tabela temporária reside no fato que os dados permanecem ativos apenas durante o tempo de vida de sua sessão e estão disponíveis apenas para a sessão que os criou. Essa característica a torna perfeita para a geração de relatórios complexos ou para o uso em algoritmos em que o processamento da informação não possa ser acessado e ao término da execução eles não devam permanecer na tabela.

Além disso, as tabelas temporárias são mais rápidas e não geram dados de REDO, isso é óbvio pois a natureza da tabela indica que os dados não precisam ser duráveis.

O comando

Para criar uma Tabela Temporária, basta apenas utilizar o comando como se segue:

CREATE GLOBAL TEMPORARY TABLE TAB_CALCULO_VARIACAO_PRECO (
NR_SEQ_PRODUTO NUMBER(8),
VL_ATUAL NUMBER(12,2),
VL_PREVISTO NUMBER(12,2)
)
ON COMMIT DELETE ROWS;

Note, que a diferenciação de uma tabela comum para uma tabela temporária é o uso da palavra-chave GLOBAL TEMPORARY e a definição se os dados serão deletados após o commit (ON COMMIT DELETE ROWS) ou mantidos ON COMMIT PRESERVE ROWS, sendo que essa opção também é opcional e pode ser ignorada, nesse caso o padrão será deletar os registros após o COMMIT.

A explicação

O que torna a tabela temporária mais rápida é o fato dela existir apenas na PGA (área privativa da sessão) que a criou, sendo este um recurso de memória - e naturalmente com melhor performance. Caso a tabela necessite expandir e a PGA da sessão não seja suficiente, ela poderá ser escrita diretamente em disco, ignorando o buffer cache.

Meus testes

Para testar a performance das tabelas temporárias em relação às tabelas regulares, foi realizada a operação de consulta e inserção de dados em tabelas com 1 milhão, 10 milhões e 30 milhões de registros, que por sua vez foram obtidos à partir da DBA_OBJECTS.

1 - Consulta de dados
Nesse teste não houve grandes diferenças entre os diferentes parâmetros avaliados. Contudo, o consistent gets (quantidade de leituras consistentes para obter os dados dos data blocks) teve um ganho expressivo na tabela temporária de 30 milhões de registros (426.373) em relação a tabela regular (708.405), sendo que em 10 milhões de registros a diferença foi bem próxima.

Descrição 1 milhão regs. 10 milhões regs. 30 milhões regs.
Tab. Regular Tab. Temp. Tab. Regular Tab. Temp. Tab. Regular Tab. Temp.
recursive calls 0 0 4 0 5 0
db block gets 0 0 0 0 0 0
consistents gets 14.165 14.216 141.707 142.127 708.405 426.373
physical reads 14.161 14.212 141.618 142.103 425.225 426.305
redo size 0 116 0 116 0 72
bytes sent via SQL*Net to client 1.715 1.715 1.729 1.729 1.737 1.737
bytes received via SQL*Net from client 545 545 545 545 545 545
SQL*Net roundtrips to/from client 4 4 4 4 4 4
sorts (memory) 0 0 0 0 0 0
sorts (disk) 0 0 0 0 0 0
rows processed 43 43 43 43 43 43


2 - Inserção de dados
Nesse teste o parâmetro consistents gets foi favorável em relação as tabelas temporárias em todas as situações, sendo que o total representou cerca de 50% a 60% do total observado nas tabelas regulares. O aspecto que mais chamou a atenção foi o redo size, por exemplo, a tabela regular com 30 milhões de registros apresentou um total de 3,26 GB de redo, enquanto a tabela temporária apresentou cerca de 162 MB (4%).

Descrição 1 milhão regs. 10 milhões regs. 30 milhões regs.
Tab. Regular Tab. Partic. Tab. Regular Tab. Partic. Tab. Regular Tab. Partic.
recursive calls 535 649 894 1.507 4.787 4.000
db block gets 78.552 79.530 783.216 796.104 2.353.981 2.385.242
consistents gets 42.846 28.443 426.983 282.621 1.568.546 847.548
physical reads 13.889 14.255 141.682 143.287 422.265 429.671
redo size 116.611.676 5.689.080 1.161.010.532 56.815.008 3.503.453.988 170.448.696
bytes sent via SQL*Net to client 846 847 847 847 847 847
bytes received via SQL*Net from client 817 830 819 832 819 832
SQL*Net roundtrips to/from client 3 3 3 3 3 3
sorts (memory) 1 3 1 1 1 1
sorts (disk) 0 0 0 0 0 0
rows processed 1.000.000 1.000.000 10.000.000 10.000.000 30.000.000 30.000.000

Contudo, esperava-se que não houvesse qualquer geração de redo nas tabelas temporárias, pois por definição os dados não precisam ser recuperados. Isso se deve ao fato que o REDO gerado no teste não se refere as tabelas e sim aos segmentos de UNDO gerados durante o processo (essa questão foi apresentada em uma discussão do site AskTOM). Para que nenhum redo seja gerado deve-se utilizar o hint /*+ APPEND */ no INSERT.

Para o teste do hint dobramos a quantidade de registros presentes na tabela com 10 milhões de registros, o comando e os dados podem ser observados a seguir.

INSERT /*+ APPEND */ INTO T_DBA_OBJECTS_10M_TEMP (SELECT * FROM T_DBA_OBJECTS_10M_TEMP);

Descrição 10 milhões regs.
recursive calls 2.224
db block gets 306.403
consistents gets 284.189
physical reads 259.177
redo size 68
bytes sent via SQL*Net to client 831
bytes received via SQL*Net from client 846
SQL*Net roundtrips to/from client 3
sorts (memory) 1
sorts (disk) 0
rows processed 20.000.000

Referências

https://asktom.oracle.com/pls/apex/asktom.search?tag=redo-for-temporary-tables
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#ADMIN01503

Adauto Santos

Analista de Sistemas Senior na Unimed Regional Maringá. Formado em Informática pela Universidade Estadual de Maringá. Pós-Graduado em Administração de Banco de Dados pela Metrocamp e Business Intelligence pela Unicesumar.

WebSite: http://www.adautosantos.com.br/
LinkedIn: http://www.linkedin.com/in/adauto-santos-91278029
Facebook: https://www.facebook.com/adauto.santos.946

Envie seu comentário

Mensagem
Nome
E-mail

Comentários

Não há comentários no artigo. Seja o primeiro a comentar!