1. The Need for Intermediate Storage

In complex analytical reporting or heavy batch processing (like Oracle RMS replenishment or stock ledger rollups), you rarely extract data in a single clean pass. You often need to:

1. Aggregate a large baseline dataset.
2. Filter it heavily.
3. Join the intermediate result to several other massive tables.

How you store that intermediate dataset dictates the performance of your entire batch. The two dominant choices are Common Table Expressions (CTEs) and Global Temporary Tables (GTTs).

2. Common Table Expressions (WITH Clause)

CTEs allow you to define a subquery block at the beginning of your SQL statement.

WITH regional_sales AS (
    SELECT region_id, SUM(sales_amt) as total_sales
    FROM sales
    WHERE sales_date = TRUNC(SYSDATE)
    GROUP BY region_id
)
SELECT r.region_name, rs.total_sales
FROM regional_sales rs
JOIN regions r ON rs.region_id = r.region_id;

How the Optimizer Handles CTEs

The Oracle Optimizer has two choices when encountering a CTE:

1. Inline it: It merges the CTE query directly into the main query block (as if it was just a subquery).
2. Materialize it: It executes the CTE once, stores the result in a hidden temporary segment in the TEMP tablespace, and treats it like a table for the rest of the query.

Pros of CTEs:

• Extremely clean, readable code.
• Zero metadata overhead (no DDL required to create tables).
• The optimizer has a global view of the entire query and can push predicates (filters) down into the CTE automatically.

Cons of CTEs:

• Data only exists for the duration of the single SELECT statement.
• You cannot index a CTE.

3. Global Temporary Tables (GTTs)

GTTs are persistent database objects (the table definition exists in the data dictionary permanently), but the data inside them is completely private to the session that inserted it.

CREATE GLOBAL TEMPORARY TABLE gtt_daily_sales (
    region_id NUMBER,
    total_sales NUMBER
) ON COMMIT PRESERVE ROWS; 
-- OR: ON COMMIT DELETE ROWS;

GTT Characteristics

• Transaction or Session Scoped: ON COMMIT DELETE ROWS clears the table the moment you issue a COMMIT. PRESERVE ROWS keeps the data until you disconnect your session.
• Indexable: You can create indexes on GTTs.
• Undo/Redo Overhead: While GTTs do not generate redo for the data blocks, they do generate undo (for rollback capability), which in turn generates redo for the undo blocks. They are not "free" operations.

Pros of GTTs:

• You can persist data across multiple separate PL/SQL steps.
• You can add indexes to dramatically speed up subsequent joins.
• You can gather statistics specifically on the GTT for complex execution plans.

Cons of GTTs:

• Generates undo/redo overhead during inserts.
• The definition is permanent, leading to a cluttered schema if abused.

4. Private Temporary Tables (Oracle 18c+)

Introduced in Oracle 18c, Private Temporary Tables (PTTs) fix the primary complaint about GTTs: persistent metadata.

A PTT only exists in memory for the duration of a transaction or session. When the session ends, both the data and the table definition itself vanish.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_sales (
    region_id NUMBER,
    total_sales NUMBER
) ON COMMIT DROP DEFINITION;

5. When to use which?