Criando e usando tabelas temporárias

As tabelas temporárias são úteis em aplicativos em que um conjunto de resultados deve ser armazenado em buffer (persistido temporariamente), talvez porque seja construído executando várias operações DML. Por exemplo, considere o seguinte:

Um aplicativo de reservas de companhias aéreas baseado na Web permite que um cliente crie vários itinerários opcionais. Cada itinerário é representado por uma linha em uma tabela temporária. O aplicativo atualiza as linhas para refletir as alterações nos itinerários. Quando o cliente decide qual itinerário deseja usar, o aplicativo move a linha desse itinerário para uma tabela persistente.

Durante a sessão, os dados do itinerário são privados. No final da sessão, os itinerários opcionais são descartados. A definição de uma tabela temporária é visível para todas as sessões, mas os dados em uma tabela temporária são visíveis apenas para a sessão que insere os dados na tabela.

Use a instrução CREATE GLOBAL TEMPORARY TABLE para criar uma tabela temporária. A cláusula ON COMMIT indica se os dados na tabela são específicos de transação (é o padrão) ou específicos de sessão, cujas implicações são as seguintes:

Esta instrução cria uma tabela temporária específica da transação:

CREATE GLOBAL TEMPORARY TABLE admin_work_area
      (startdate DATE,
       enddate DATE,
       class CHAR(20))
    ON COMMIT DELETE ROWS;

Os índices podem ser criados em tabelas temporárias. Eles também são temporários e os dados no índice têm o mesmo escopo de transação ou sessão que os dados na tabela subjacente.

Por padrão, as linhas em uma tabela temporária são armazenadas no espaço de tabela temporário padrão do usuário que a cria. No entanto, você pode atribuir uma tabela temporária a outro espaço de tabela após a criação da tabela temporária usando a cláusula TABLESPACE de CREATE GLOBAL TEMPORARY TABLE. Você pode usar esse recurso para economizar espaço usado por tabelas temporárias. Por exemplo, se você precisar executar muitas operações pequenas de tabela temporária e o espaço de tabela temporário padrão estiver configurado para operações de classificação e, assim, usar um tamanho de grande extensão, essas pequenas operações consumirão muito espaço em disco desnecessário. Nesse caso, é melhor alocar um segundo espaço de tabela temporário com um tamanho de extensão menor.

As duas instruções a seguir criam um espaço de tabela temporário com um tamanho de extensão de 64 KB e, em seguida, uma nova tabela temporária nesse espaço de tabela.

CREATE TEMPORARY TABLESPACE tbs_t1 
    TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON
    MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS
      TABLESPACE tbs_t1;

Diferentemente das tabelas permanentes, as tabelas temporárias e seus índices não alocam automaticamente um segmento quando são criados. Em vez disso, os segmentos são alocados quando o primeiro INSERT (ou CREATE TABLE AS SELECT) é executado. Isso significa que, se um SELECT, UPDATE ou DELETE for executado antes do primeiro INSERT, a tabela parecerá vazia.

As operações DDL (exceto TRUNCATE) são permitidas em uma tabela temporária existente apenas se nenhuma sessão estiver atualmente vinculada a essa tabela temporária. Se você reverter uma transação, os dados inseridos serão perdidos, embora a definição da tabela persista.

Uma tabela temporária específica da transação permite apenas uma transação por vez. Se houver várias transações autônomas em um único escopo de transação, cada transação autônoma poderá usar a tabela apenas assim que a anterior for confirmada.

Como os dados em uma tabela temporária são, por definição, temporários, o backup e a recuperação de dados temporários da tabela não estão disponíveis no caso de uma falha do sistema. Para se preparar para essa falha, você deve desenvolver métodos alternativos para preservar dados temporários da tabela.

Referência

Creating a Temporary Table