Optimizing Large‑Scale Oracle Queries with Temporary Tables: Strategies and Trade‑offs

Development Requirement

A system needs to first retrieve IDs and some columns from a main table (or the main table joined with a few child tables). Using those IDs, up to ten child tables must be queried for matching records. The main table holds about 20 million rows; each child table contains over a million rows, potentially reaching 50 million rows total. A single main‑table row may correspond to many child‑table rows.

Current logic:

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

For each child table, execute an IN clause using the collected IDs. The number of SQL statements equals the number of child tables involved.

Problems with this approach

When the ID list exceeds about 100 values, the optimizer may choose a full‑table scan for the child tables, leading to poor performance on million‑row tables. Moreover, each additional child table adds another SQL execution, inflating round‑trips between application and database.

Potential Improvement Strategies

Three main alternatives are considered:

Single‑SQL solution

(1.1) Join the main table with all child tables. This can produce a massive Cartesian product because of one‑to‑many relationships, risking out‑of‑memory errors.

(1.2) Create a UNION ALL view of all child tables and join it with the main table. Example:

SELECT A.ID, A.NAME
FROM T_ZHUBIAO A,
     (SELECT ID, NAME FROM T_ZIBIAO1
      UNION ALL
      SELECT ID, NAME FROM T_ZIBIAO2) B
WHERE A.NAME = 'A' AND A.ID = B.ID;

This approach forces each child table to use an index‑only scan, then combines results via a nested‑loop join. However, differing column sets across child tables make the UNION ALL view cumbersome.

Use a temporary table Store the IDs obtained from step 1 in a global temporary table. Subsequent child‑table queries join against this temporary table, allowing index scans on primary/foreign keys and 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.

Split IN‑clauses into batches Keep the original logic but break a large IN list (e.g., 30 IDs per batch) into multiple SQL statements. This reduces the chance of full‑table scans while limiting the number of statements compared to executing one per child table.

In practice, the team adopted strategy 3 because it required minimal code changes—just batching the IN clause. If performance tests later show it insufficient, they will evaluate strategy 2.

Temporary Table Overview and Experiments

Temporary tables store intermediate results per session. Data is automatically removed when the session ends or, depending on the ON COMMIT clause, when the transaction commits.

Creating a transaction‑level temporary table

CREATE GLOBAL TEMPORARY TABLE test (id NUMBER, name VARCHAR2(10)) ON COMMIT DELETE ROWS;

Verification:

SELECT table_name, tablespace_name, temporary FROM dba_tables WHERE owner='BISAL';

Session 1:

INSERT INTO test VALUES (1, 'a');
SELECT * FROM test;

Result shows the row is visible. Session 2 sees no rows, confirming session isolation.

After COMMIT in session 1, the row disappears, demonstrating that ON COMMIT DELETE ROWS clears data at transaction end.

Creating a session‑level temporary table

CREATE GLOBAL TEMPORARY TABLE test (id NUMBER, name VARCHAR2(10)) ON COMMIT PRESERVE ROWS;

Data persists across commits within the same session but is removed when the session ends.

Summary and Best Practices

Temporary tables are straightforward to use, behaving like regular tables aside from storage nuances. Key considerations:

Use a dedicated temporary tablespace if the application performs many sorts or large temporary‑table operations to avoid contention.

When employing session‑level temporary tables with connection pooling, ensure a single transaction uses the same database session to comply with temporary‑table rules.

Overall, the choice among the three strategies depends on performance test results and development effort, with the temporary‑table approach offering a balanced trade‑off between query efficiency and code complexity.