When creating a temporary table, SQL offers ON COMMIT PRESERVE ROWS, ON COMMIT DELETE ROWS, and ON COMMIT DROP. What does each do, and when would you choose each?

middleshould knowfreq 36%

answer

PRESERVE = until session end (usual default)
DELETE ROWS = implicit truncate each commit
DROP = object gone at commit
Oracle GTT: permanent definition, DELETE ROWS default
pooling + PRESERVE = stale rows in the next request

basics

~20 s

PRESERVE ROWS keeps the table and its data until the session ends (the usual default). DELETE ROWS keeps the table definition but empties it at every COMMIT. DROP removes the whole table at COMMIT. Choose DROP for one-transaction scratch space, DELETE ROWS to reuse a definition across many transactions, PRESERVE for session-long staging.

solid answer

~60 s

The ON COMMIT clause controls what happens to a temporary table at the end of each transaction:

PRESERVE ROWS — table and rows survive the commit and live until the session disconnects. The common default. Right for session-long staging across several transactions, and the option that interacts badly with connection pooling if you forget to clean up.
DELETE ROWS — the definition persists for the session but the contents are emptied at every commit, effectively an implicit TRUNCATE. Right for a procedure called in many transactions per connection: you create the table once, avoid repeated catalog churn, and every transaction starts empty. This is Oracle's default for global temporary tables.
DROP — the table itself disappears at commit. Right for genuinely one-transaction scratch space; nothing leaks into a subsequent request on a pooled connection. The cost is catalog work on every transaction that creates it.

The practical rule: DROP for safety within a single transaction, DELETE ROWS when you need the same shape repeatedly and want to pay catalog cost once, PRESERVE only when the workflow genuinely spans transactions.

The `ON COMMIT` clause controls what happens to a temporary table at the end of each transaction: - **PRESERVE ROWS** — table and rows survive the commit and live until the session disconnects. The common default. Right for session-long staging across several transactions, and the option that interacts badly with connection pooling if you forget to clean up. - **DELETE ROWS** — the definition persists for the session but the contents are emptied at every commit, effectively an implicit TRUNCATE. Right for a procedure called in many transactions per connection: you create the table once, avoid repeated catalog churn, and every transaction starts empty. This is Oracle's default for global temporary tables. - **DROP** — the table itself disappears at commit. Right for genuinely one-transaction scratch space; nothing leaks into a subsequent request on a pooled connection. The cost is catalog work on every transaction that creates it. The practical rule: **DROP** for safety within a single transaction, **DELETE ROWS** when you need the same shape repeatedly and want to pay catalog cost once, **PRESERVE** only when the workflow genuinely spans transactions.

code

sql · 8 lines

sql

-- disappears at COMMIT; safe under connection pooling
CREATE TEMP TABLE batch_ids (id bigint PRIMARY KEY) ON COMMIT DROP;
INSERT INTO batch_ids SELECT id FROM inbox WHERE state = 'NEW' LIMIT 5000;
UPDATE inbox SET state = 'CLAIMED' WHERE id IN (SELECT id FROM batch_ids);
COMMIT;

-- created once per session; every transaction starts empty
CREATE TEMP TABLE work_rows (id bigint, amount numeric) ON COMMIT DELETE ROWS;

go deeper

for a junior

Recall the three behaviours precisely: keep rows, empty rows at commit, drop the table at commit.

for a middle

Justify a choice with the two axes — does data outlive the transaction, and creation frequency versus use frequency — and mention Oracle's global temporary tables as the DELETE ROWS archetype.

for a senior

Lead with the pooling hazard and the statistics and plan-recompilation consequences of emptying a table each transaction.

for a principal

Set a convention: transaction-scoped by default so no state can leak across pooled requests, with session-scoped tables allowed only where a documented multi-transaction workflow requires them.

The clause and what it controls

A temporary table has two independent lifetimes: how long the definition lives, and how long the rows live. ON COMMIT is where you set the second, and in one case the first as well.

ON COMMIT PRESERVE ROWS. Nothing happens at commit. The table and its contents remain until the session ends. This is the default in PostgreSQL, SQL Server (#temp tables), and MySQL. It fits work that spans transactions — load in one transaction, verify in a second, apply in a third, all reading the same staged rows.

ON COMMIT DELETE ROWS. The table's definition persists for the session, but each commit removes all rows. Semantically an automatic TRUNCATE at every transaction boundary. The value is amortization: a procedure invoked thousands of times per connection creates the table once and every invocation still begins with a clean, empty table. Oracle's global temporary tables default to this, and their definition is even permanent — declared once at deploy time and shared by all sessions, with per-session contents.

ON COMMIT DROP. The table object is removed at commit. This is the strongest hygiene guarantee: no stale rows and no stale definition can reach the next transaction, which matters enormously with connection pooling, where the next transaction on this physical connection belongs to a different logical request. The price is that every transaction pays the catalog cost of creating and dropping the table.

Choosing

Ask two questions.

Does the data need to outlive the transaction? If no, you are choosing between DROP and DELETE ROWS. If yes, you need PRESERVE and you now own the cleanup problem.

How often is the table created relative to how often it is used? If a procedure runs many times per connection, DELETE ROWS on a table created once per session amortizes the catalog work; DROP pays it every time. If the routine runs once per connection, or if you cannot guarantee the definition still exists (session reset, pool recycling), DROP is simpler and safer.

A common, robust pattern for high-frequency procedures: create the table once per session with DELETE ROWS, guarded so re-creation is a no-op, then rely on the automatic emptying. A common robust pattern for occasional batch work: ON COMMIT DROP and stop thinking about it.

Interactions worth knowing

Connection pooling. PRESERVE ROWS plus a pool is the classic bug: request A leaves rows behind, request B on the same physical connection sees them and either double-counts or fails a uniqueness check. Symptoms are non-deterministic and load-dependent, because they only occur when the pool happens to hand back the same connection. DROP or DELETE ROWS removes the failure mode structurally; a pool configured to reset the session on checkout removes it too, if you can rely on it.

Statistics. Emptying rows at commit invalidates whatever statistics you gathered. A procedure that repopulates a DELETE ROWS table each transaction usually needs to gather statistics again, or accept plans built on stale or default estimates. Some engines add explicit machinery around this because the pattern is so common.

Plan caching. Statements inside a routine that reference a temporary table are tied to that session's object. Recreating or emptying the table can force recompilation, which costs CPU but also protects against a plan built for a very differently sized version of the same table.

Open cursors and holdability. A cursor left open across a commit that reads a table dropped or emptied at that commit will fail or return nothing. If you use WITH HOLD-style cursors, check the interaction.

Rollback. These clauses fire on commit. On rollback, a table created inside the rolled-back transaction disappears because its DDL is rolled back too, in engines with transactional DDL.

Answering well

Define all three precisely, then justify a choice with the two axes — does the data outlive the transaction, and how often is the object created versus used — and finish with the connection-pooling hazard, which is the practical reason this clause is worth caring about at all.

## The clause and what it controls A temporary table has two independent lifetimes: how long the **definition** lives, and how long the **rows** live. `ON COMMIT` is where you set the second, and in one case the first as well. **ON COMMIT PRESERVE ROWS.** Nothing happens at commit. The table and its contents remain until the session ends. This is the default in PostgreSQL, SQL Server (`#temp` tables), and MySQL. It fits work that spans transactions — load in one transaction, verify in a second, apply in a third, all reading the same staged rows. **ON COMMIT DELETE ROWS.** The table's definition persists for the session, but each commit removes all rows. Semantically an automatic TRUNCATE at every transaction boundary. The value is amortization: a procedure invoked thousands of times per connection creates the table once and every invocation still begins with a clean, empty table. Oracle's global temporary tables default to this, and their definition is even permanent — declared once at deploy time and shared by all sessions, with per-session contents. **ON COMMIT DROP.** The table object is removed at commit. This is the strongest hygiene guarantee: no stale rows and no stale definition can reach the next transaction, which matters enormously with connection pooling, where the next transaction on this physical connection belongs to a different logical request. The price is that every transaction pays the catalog cost of creating and dropping the table. ## Choosing Ask two questions. *Does the data need to outlive the transaction?* If no, you are choosing between DROP and DELETE ROWS. If yes, you need PRESERVE and you now own the cleanup problem. *How often is the table created relative to how often it is used?* If a procedure runs many times per connection, DELETE ROWS on a table created once per session amortizes the catalog work; DROP pays it every time. If the routine runs once per connection, or if you cannot guarantee the definition still exists (session reset, pool recycling), DROP is simpler and safer. A common, robust pattern for high-frequency procedures: create the table once per session with DELETE ROWS, guarded so re-creation is a no-op, then rely on the automatic emptying. A common robust pattern for occasional batch work: ON COMMIT DROP and stop thinking about it. ## Interactions worth knowing **Connection pooling.** PRESERVE ROWS plus a pool is the classic bug: request A leaves rows behind, request B on the same physical connection sees them and either double-counts or fails a uniqueness check. Symptoms are non-deterministic and load-dependent, because they only occur when the pool happens to hand back the same connection. DROP or DELETE ROWS removes the failure mode structurally; a pool configured to reset the session on checkout removes it too, if you can rely on it. **Statistics.** Emptying rows at commit invalidates whatever statistics you gathered. A procedure that repopulates a DELETE ROWS table each transaction usually needs to gather statistics again, or accept plans built on stale or default estimates. Some engines add explicit machinery around this because the pattern is so common. **Plan caching.** Statements inside a routine that reference a temporary table are tied to that session's object. Recreating or emptying the table can force recompilation, which costs CPU but also protects against a plan built for a very differently sized version of the same table. **Open cursors and holdability.** A cursor left open across a commit that reads a table dropped or emptied at that commit will fail or return nothing. If you use `WITH HOLD`-style cursors, check the interaction. **Rollback.** These clauses fire on *commit*. On rollback, a table created inside the rolled-back transaction disappears because its DDL is rolled back too, in engines with transactional DDL. ## Answering well Define all three precisely, then justify a choice with the two axes — does the data outlive the transaction, and how often is the object created versus used — and finish with the connection-pooling hazard, which is the practical reason this clause is worth caring about at all.

Why is ON COMMIT DROP often the safest choice in an application that uses a connection pool?
A pooled physical connection is reused by unrelated logical requests, so anything session-scoped that survives the transaction can be observed by the next request. Dropping the table at commit means neither stale rows nor a stale definition can leak forward, so a request always starts from a known state. The tradeoff is paying catalog create-and-drop cost on every transaction, which matters only at high call rates.
What do you have to redo after each commit if a temporary table is declared ON COMMIT DELETE ROWS?
Repopulate it, and gather statistics again if the plans that read it depend on realistic cardinality — the automatic emptying discards both the rows and the statistical picture built from them. Indexes on the table survive because the definition survives, so they do not need recreating. Skipping the statistics step is a frequent cause of plans built on default estimates for a table that is actually large.

saying these in an interview costs you the question

Assuming ON COMMIT DELETE ROWS drops the table itself.
Believing PRESERVE ROWS keeps data across sessions rather than only across transactions within one session.
Relying on session-scoped temp tables under a connection pool without any reset strategy.
Thinking the clause fires on rollback as well as commit.
Forgetting that emptying the table discards its statistics.

When creating a temporary table, SQL offers ON COMMIT PRESERVE ROWS, ON COMMIT DELETE ROWS, and ON COMMIT DROP. What does each do, and when would you choose each?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The clause and what it controls

Choosing

Interactions worth knowing

Answering well

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics3 topics

The clause and what it controls

Choosing

Interactions worth knowing

Answering well

answer

go deeper

context