Temporary table in context of ORACLE SQL

In Oracle SQL, the primary type of temporary table is the Global Temporary Table (GTT). However, depending on the use case, other approaches can also simulate temporary storage or temporary tables. Here are the key types and approaches:

1. Global Temporary Tables (GTT)

These are the main temporary tables in Oracle. The table structure is permanent, but the data is temporary and session/transaction-specific.

Variations:

ON COMMIT DELETE ROWS: Clears data after each transaction.

ON COMMIT PRESERVE ROWS: Retains data until the session ends.

Example:

CREATE GLOBAL TEMPORARY TABLE temp_table (
id NUMBER,
name VARCHAR2(100)
) ON COMMIT DELETE ROWS;

1. Private Temporary Tables (PTT) (Introduced in Oracle 18c)

These are session-private tables that exist only for the duration of a session or a transaction. The table and its data are temporary.

Features:

Automatically dropped at the end of the session or transaction.

Prefixed with ORA$PTT_ internally.

Syntax:

CREATE PRIVATE TEMPORARY TABLE temp_table_name (
column1 datatype,
column2 datatype
) ON COMMIT {DROP | PRESERVE} DEFINITION;

Example:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_table (
id NUMBER,
value VARCHAR2(100)
) ON COMMIT DROP DEFINITION;

INSERT INTO ora$ptt_temp_table VALUES (1, 'Data');
-- Table and data are automatically dropped at the end of the session/transaction.

1. Temporary Views

Although not actual tables, views based on inline subqueries or Common Table Expressions (CTEs) can simulate temporary tables for read-only operations.

Example:

CREATE VIEW temp_view AS
SELECT *
FROM employees
WHERE department_id = 10;

SELECT * FROM temp_view;

1. With Clause (Common Table Expressions - CTEs)

The WITH clause is a type of temporary data storage for query processing, valid for the duration of the SQL statement.

Example:

WITH temp_cte AS (
SELECT employee_id, salary
FROM employees
WHERE department_id = 20
)
SELECT * FROM temp_cte WHERE salary > 5000;

1. Inline Views (Derived Tables)

Inline views are subqueries within the FROM clause that act as temporary tables during query execution.

Example:

SELECT *
FROM (
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
) temp_inline_view
WHERE emp_count > 10;

1. Materialized Views with ON DEMAND Refresh

Materialized views can be used as temporary storage when refreshed ON DEMAND. They persist data until explicitly refreshed or dropped.

Example:

CREATE MATERIALIZED VIEW temp_materialized_view
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT * FROM employees WHERE department_id = 30;

1. Temporary Table-like Behavior Using PL/SQL Collections

PL/SQL collections (like TABLE, VARRAY, or ASSOCIATIVE ARRAY) can hold temporary data for processing within PL/SQL blocks.

Example:

DECLARE
TYPE temp_table IS TABLE OF employees%ROWTYPE;
temp_data temp_table;
BEGIN
SELECT * BULK COLLECT INTO temp_data FROM employees WHERE department_id = 40;

FOR i IN temp_data.FIRST .. temp_data.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(temp_data(i).employee_name);
END LOOP;

END;

1. Staging Tables (Regular Tables Used Temporarily)

When other options are unsuitable, regular tables can act as staging areas for temporary data, but you must manage data cleanup explicitly.

Example:

CREATE TABLE staging_table (
id NUMBER,
data VARCHAR2(100)
);

-- Insert and process data temporarily
INSERT INTO staging_table VALUES (1, 'Temporary Data');
-- Cleanup
TRUNCATE TABLE staging_table;

Summary of Use Cases:

Each approach has unique advantages based on the specific requirements of your use case.