Oracle Temporary Tables ⏳
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 hitCOMMIT.
💻 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.