Reducing Redo Overhead with Oracle Global Temporary Tables

The Cost of Intermediate Data

Many developers handle complex multi-step calculations by creating a permanent "staging" table. They insert intermediate results, perform several joins or aggregations, and then truncate the table for the next run. While this works, it creates a large amount of unnecessary redo log generation. Every insert and delete in a permanent table is logged to ensure recoverability, which consumes I/O and can slow down the entire database during high-concurrency periods.

The solution for session-specific intermediate data is the Global Temporary Table (GTT). A GTT stores data that is private to your session and resides in temporary segments, drastically reducing the logging overhead and eliminating locking contention between users.

How Global Temporary Tables Differ from Permanent Tables

The primary distinction of a GTT is that the definition (the columns, types, and indexes) is permanent and stored in the data dictionary, but the data is transient. When Session A inserts rows into a GTT, Session B cannot see them, even if they are querying the exact same table name. This isolation means you don't need to manage unique IDs or "batch IDs" to keep different users' data separate.

Because GTT data lives in temporary segments rather than regular datafiles, DML against it generates far less redo than the same operations on a permanent table. That is where the I/O savings come from.

Choosing Your Persistence Strategy

When creating a GTT, you must decide how long the data should live. Oracle provides two distinct behaviors:

• ON COMMIT DELETE ROWS: This is a transaction-specific table. As soon as you issue a COMMIT or ROLLBACK, the data is wiped. This is ideal for a single complex operation that needs a temporary landing zone for a few rows before a final update.
• ON COMMIT PRESERVE ROWS: This is a session-specific table. Data persists across multiple commits and remains available until the session ends or the data is manually deleted. This is best for multi-step workflows where you need to reference the same intermediate set across several different transactions.

Worked Example: Processing a Batch Update

Imagine a scenario where you need to calculate a discount for a set of orders and then apply those discounts to a permanent ORDERS table. Instead of using a permanent staging table, use a GTT.

Step 1: Create the GTT

Run this once, as a user with the CREATE TABLE privilege. The definition stays in the schema permanently.

CREATE GLOBAL TEMPORARY TABLE tmp_discount_calc (
    order_id NUMBER,
    calc_discount NUMBER
) ON COMMIT PRESERVE ROWS;

Step 2: Populate and Use

Run these commands within your application session, for example in SQL*Plus or SQLcl.

-- Insert calculated values into the GTT
INSERT INTO tmp_discount_calc (order_id, calc_discount)
SELECT order_id, (total * 0.10) FROM orders WHERE status = 'PENDING';

COMMIT; -- Data remains because of PRESERVE ROWS

-- Apply the discounts to the permanent table
UPDATE orders o
SET o.discount = (SELECT t.calc_discount FROM tmp_discount_calc t WHERE t.order_id = o.order_id)
WHERE EXISTS (SELECT 1 FROM tmp_discount_calc t WHERE t.order_id = o.order_id);

COMMIT;

Verification: To verify that the data is isolated, open a second session and run SELECT COUNT(*) FROM tmp_discount_calc;. It should return 0, even while the first session still holds its data. You can also confirm the definition is shared by checking USER_TABLES, where the table appears with TEMPORARY = 'Y'.

Trade-offs and Resource Constraints

While GTTs reduce redo log pressure, they shift the burden to the temporary tablespace. Because GTTs use temporary segments, excessive data volume or long-running sessions that fail to clear their data can lead to ORA-01652: unable to extend temp segment errors.

There are two more limitations worth noting. First, indexes on GTTs are allowed, but they are also session-specific and consume temporary segment space, so index only when the row volume justifies it. Second, GTTs are not for persistence: if the instance restarts or the session disconnects, all data in the GTT is lost. If your process requires a checkpoint that survives a crash, you must use a permanent table.

Monitoring and Cleanup

To monitor how much space your GTTs are consuming in real time, query the V$TEMPSEG_USAGE view (requires privileges to query dynamic performance views, typically granted via a DBA role or SELECT_CATALOG_ROLE). This helps you identify whether a specific session is bloating the temporary tablespace.

SELECT session_addr, contents, blocks
FROM v$tempseg_usage;

If you are using ON COMMIT PRESERVE ROWS, it is good practice to explicitly TRUNCATE the GTT at the end of your business logic to free up temporary segments immediately rather than waiting for the session to terminate.

Actionable Takeaway

The next time you reach for a permanent staging table, ask whether the data ever needs to outlive the session. If the answer is no, create a GTT once, choose the commit behavior that matches your workflow, and verify isolation with a second session. You will cut redo generation, remove cross-session contention, and simplify your cleanup logic — just keep an eye on temporary tablespace usage as volumes grow.