Optimizing Large-Scale Oracle Queries with Temporary Tables

Problem Statement

A business requirement involves first retrieving IDs and some columns from a main table (≈20 million rows) and then using those IDs to query up to ten related child tables, each containing millions of rows. The current implementation runs a separate IN‑clause query for each child table.

Drawbacks of the Current Approach

Because the list of IDs can exceed 100 values, the IN clauses often force the optimizer to choose full‑table scans on the child tables, which is inefficient for tables of this size. Additionally, executing a separate SQL statement for each child table can result in many queries per request, increasing overhead.

Proposed Solution Options

Single‑SQL Solution

(1.1) Join the main table with all child tables. This can produce a massive Cartesian product and risk out‑of‑memory errors.

(1.2) Use UNION ALL to combine child tables into a view and join the view with the main table. This allows each child table to be accessed via an indexed scan, but requires identical column types across child tables, reducing flexibility.

Temporary Table Approach Insert the result set from step 1 into a global temporary table, then join each child table with this temporary table. The temporary table can be defined with ON COMMIT DELETE ROWS (transaction‑level) or ON COMMIT PRESERVE ROWS (session‑level), enabling indexed lookups and avoiding large IN lists.

Hybrid Split‑IN Strategy Keep the original query logic but split the IN list into smaller batches (e.g., 30 IDs per query). This reduces the chance of full‑table scans but increases the number of SQL executions.

In practice, the team currently uses option 3 because it requires minimal code changes. If performance tests show it meets non‑functional requirements, it will remain; otherwise, they will evaluate option 2.

Temporary Table Overview

Global temporary tables store data privately per session (or per transaction). Data is automatically removed when the session ends (or when the transaction commits, depending on the ON COMMIT clause). They support indexes, views, and triggers like regular tables.

ON COMMIT Options

Transaction‑level ( ON COMMIT DELETE ROWS ) : Data is cleared at the end of each transaction.

Session‑level ( ON COMMIT PRESERVE ROWS ) : Data persists for the duration of the session and is only removed when the session ends.

Example Commands

Create a transaction‑level temporary table:

create global temporary table test (id number, name varchar2(10)) on commit delete rows;

Verify its metadata:

select table_name, tablespace_name, temporary from dba_tables where owner='BISAL';

Insert and query data in session 1:

insert into test values(1, 'a');

select * from test;

Result:

ID  NAME
1   a

In session 2, the table is empty, demonstrating session isolation: select * from test; -- no rows selected Commit in session 1 clears the data for transaction‑level tables:

commit;

select * from test; -- no rows selected

Create a session‑level temporary table:

create global temporary table test (id number, name varchar2(10)) on commit preserve rows;

Data inserted in session 1 remains after a commit but disappears when the session ends.

Conclusion and Practical Tips

Temporary tables are straightforward to use and behave like regular tables aside from storage differences. Key considerations include allocating a dedicated temporary tablespace for high‑volume workloads and ensuring that connection‑pooling frameworks keep the same session for a transaction when using session‑level temporary tables.