Global Temporary Tables (GTT) in Oracle

Global Temporary Tables (GTTs) in Oracle are used to store session-specific data temporarily during a transaction or session. These tables help isolate user operations and avoid permanent storage, making them ideal for intermediate processing like staging or batch operations. When defined with ON COMMIT DELETE ROWS, all rows inserted into the GTT are deleted automatically after each transaction commit — useful for transactional processes like multi-step forms or temporary validations. On the other hand, ON COMMIT PRESERVE ROWS retains the data across multiple transactions within a session, which is useful when temporary data needs to be reused throughout the session, such as during report generation or session-wide transformations. Data in GTTs is private to the session, and physically, the table structure is shared but the data is not. Indexes can be created for performance within the scope of the session. By utilizing both modes, developers can optimize performance and data isolation for different use cases in real-time enterprise applications.

---

Sure, here's a comprehensive article-style explanation of Global Temporary Tables (GTT) in Oracle, covering both ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS options, with real-time use cases and examples.

---

🧾 Understanding Global Temporary Tables (GTT) in Oracle

🔹 What is a Global Temporary Table?

A Global Temporary Table (GTT) in Oracle is a table whose structure is permanent, but whose data is temporary—specific to a session or a transaction.

The data in a GTT is private to the session.

Other users can’t see your data.

The table definition is shared globally, but the data is session-specific or transaction-specific.

---

🔸 Syntax:

CREATE GLOBAL TEMPORARY TABLE table_name (
column1 datatype,
column2 datatype,
...
)
ON COMMIT DELETE ROWS; -- or ON COMMIT PRESERVE ROWS

---

🔹 Two Flavors of GTT

1. ON COMMIT DELETE ROWS

Scope: Data is deleted automatically at the end of the transaction.

Best for short-lived data you only need during one transaction.

Use case: Calculations, temporary filters, intermediate staging.

🔸 Example:

CREATE GLOBAL TEMPORARY TABLE gtt_order_summary (
order_id NUMBER,
amount NUMBER
)
ON COMMIT DELETE ROWS;

BEGIN
INSERT INTO gtt_order_summary VALUES (101, 5000);
INSERT INTO gtt_order_summary VALUES (102, 7000);

COMMIT; -- All rows in gtt_order_summary are deleted here automatically
END;

---

1. ON COMMIT PRESERVE ROWS

Scope: Data persists for the entire session until the user disconnects or explicitly deletes the rows.

Best for multi-transaction logic.

Use case: Stepwise data preparation across multiple transactions.

🔸 Example:

CREATE GLOBAL TEMPORARY TABLE gtt_product_list (
product_id NUMBER,
category VARCHAR2(50)
)
ON COMMIT PRESERVE ROWS;

-- Transaction 1
INSERT INTO gtt_product_list VALUES (201, 'Electronics');
COMMIT;

-- Transaction 2
-- Still accessible within the session
INSERT INTO gtt_product_list VALUES (202, 'Furniture');
COMMIT;

-- All rows are retained until the session ends or explicitly deleted

---

🔹 Real-World Use Cases

✅ Use Case 1: ON COMMIT DELETE ROWS

Scenario: A banking application needs to calculate interest for a list of customers temporarily before applying updates.

Data is needed only during the transaction.

You want a fresh set every time.

BEGIN
INSERT INTO gtt_interest_calc (customer_id, interest)
SELECT id, balance * 0.05 FROM customers WHERE status = 'ACTIVE';

-- Use it to update customer accounts
UPDATE customers c
SET c.balance = c.balance + (
SELECT interest FROM gtt_interest_calc g WHERE g.customer_id = c.id
);

COMMIT; -- GTT data auto-deleted
END;

---

✅ Use Case 2: ON COMMIT PRESERVE ROWS

Scenario: An e-commerce admin filters a list of products to be discounted across multiple steps or screens in the same session.

Data needs to persist across different page requests/transactions in the same session.

-- Admin selects product IDs from UI step 1
INSERT INTO gtt_selected_products (product_id) VALUES (301);
INSERT INTO gtt_selected_products (product_id) VALUES (302);
COMMIT;

-- Step 2: Apply bulk discount using same GTT
UPDATE products p
SET p.price = p.price * 0.9
WHERE p.id IN (SELECT product_id FROM gtt_selected_products);

---

🛑 Important Notes:

GTTs do not generate redo/undo for DML (Data Manipulation Language), which improves performance.

Indexes can be created on GTTs.

You can use TRUNCATE to clear GTT data early if needed.

---

🔚 Summary

Option Deletes When? Use Case

ON COMMIT DELETE ROWS After each transaction Temp results, intermediate calculations
ON COMMIT PRESERVE ROWS At session end Multi-step operations in user sessions