Utilização de tabelas temporárias no Oracle
Postado por Adauto Santos
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
