Optimizing Large-Scale Joins with Oracle Temporary Tables: Strategies & Pitfalls

Development Requirement

The project needs to first retrieve IDs (primary keys) and some fields from a master table (or the master table joined with a few child tables). Afterwards, those IDs must be used to fetch related records from up to ten child tables. The master table holds about 20 million rows, each child table contains over a million rows, and a single master row may correspond to multiple child rows.

Current Approach and Its Drawbacks

The existing logic performs two steps:

Query the master (or master + selected child tables) to obtain the qualifying IDs and other fields.

For each child table, execute a separate IN query using the collected IDs. The number of IN statements equals the number of child tables involved.

This leads to two major problems:

If the ID list exceeds about 100 values, the optimizer may choose a full‑table scan for the child tables, causing severe performance degradation on million‑row or larger tables.

Multiple child tables result in multiple SQL statements, potentially dozens of queries in a single scenario.

Proposed Solutions

Single‑SQL Join : Join the master table with all child tables in one statement. This can produce a massive Cartesian product and risk out‑of‑memory errors because of the one‑to‑many relationships.

UNION ALL View : Create a view that unions all child tables (requiring identical column types) and join the view with the master table. Each child table is accessed via an indexed scan, but the need for uniform column definitions makes this approach inflexible.

Global Temporary Table : Store the intermediate result (IDs and needed master fields) in a global temporary table. Subsequent joins with child tables use indexed lookups, avoiding large IN lists. The temporary table can be defined with ON COMMIT DELETE ROWS (transaction‑level) so Oracle automatically clears data after the transaction.

Batch IN Queries : Keep the original logic but split the ID list into batches (e.g., 30 IDs per query). This reduces the chance of full‑table scans but increases the total number of SQL statements.

In practice, the team currently uses the batch‑IN approach (solution 4) because it requires minimal code changes. If performance tests show it does not meet the non‑functional requirements, the temporary‑table method will be evaluated.

Temporary Table Overview and Experiments

Global temporary tables are session‑private; data is automatically removed at session end or on commit, depending on the ON COMMIT clause.

Creating a transaction‑level temporary table (data cleared on commit):

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

Querying the data dictionary shows TEMPORARY = Y and no tablespace name, confirming it is a temporary table.

Session behavior example :

-- Session 1
insert into test values (1, 'a');
select * from test;  -- returns the row
commit;
select * from test;  -- no rows (data removed on commit)

When the table is created with ON COMMIT PRESERVE ROWS, data persists for the duration of the session but is removed when the session ends:

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

In this mode, a COMMIT does not delete the rows; they disappear only after the session terminates.

Key Takeaways

Consider allocating a dedicated temporary tablespace if the application heavily uses temporary tables, to avoid contention with other operations that need temporary space for sorting, etc.

When using session‑level temporary tables together with a connection pool, ensure that all statements belonging to a single transaction are executed on the same database session; otherwise the temporary data may become inaccessible.