Oracle Temporary Tables ⏳¶

Prerequisites: Table Management

Mentor's Note: Sometimes you need a "Scratchpad". You want to store data for a few minutes while you do a complex calculation, but you don't want that data to stay in the database forever. That's a Temporary Table. 💡

🌟 The Scenario: The Rough Work 📝¶

Imagine a student taking an exam. - Main Table: The Final Answer Sheet. - Temp Table: The Rough Sheet where you do your rough calculations. Once you finish the exam, the rough sheet is shredded.

💻 1. Global Temporary Tables (GTT)¶

The structure is permanent, but the data is temporary.

CREATE GLOBAL TEMPORARY TABLE temp_sales (
    id    NUMBER,
    total NUMBER
) ON COMMIT PRESERVE ROWS;

- ON COMMIT PRESERVE ROWS: Data stays until your session ends (logout). - ON COMMIT DELETE ROWS: Data is wiped as soon as you hit COMMIT.

💻 2. Private Temporary Tables (Oracle 18c+)¶

Both the structure AND the data are temporary. They start with the prefix ORA$PTT_.

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_my_temp (
    id NUMBER
) ON COMMIT DROP DEFINITION;

🏗️ Architect's Note: Undo & Redo 🛡️¶

Traditional tables generate a lot of "Undo" and "Redo" logs (to allow rollbacks). - The Architect's Secret: GTTs generate much less redo log entries. This makes them significantly faster for bulk operations. - Tip: Use temp tables to break down massive, slow queries into smaller, manageable steps.