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

Development Requirement and Current Approach

A system needs to first retrieve IDs and some fields from a main table (≈20 million rows) and then use those IDs to query up to ten child tables, each containing millions of rows. The existing logic is:

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

For each child table, execute a separate SELECT … WHERE child_id IN (list_of_ids) statement.

Problems with the Current Method

When the ID list exceeds 100 values, the IN clause often forces the optimizer to choose a full‑table scan, which is costly on tables of millions of rows. Moreover, the number of child tables dictates the number of SQL statements, potentially leading to a large number of round‑trips between the application and the database.

Proposed Improvement Options

Three main alternatives are considered:

Option 1 – Single SQL with Joins : Join the main table with all child tables in one statement. This can produce a massive Cartesian product because of the one‑to‑many relationships, risking memory overflow.

Option 2 – UNION ALL View : Create a view that UNION ALLs all child tables, then join the view 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 still requires the child tables to share the same column list, which reduces flexibility.

Option 3 – Global Temporary Table : Insert the IDs obtained from the first step into a global temporary table (GTT). Subsequent child‑table queries join against this GTT, allowing index‑based lookups and avoiding large IN lists. The GTT can be defined as transaction‑level ( ON COMMIT DELETE ROWS) or session‑level ( ON COMMIT PRESERVE ROWS).

Using Global Temporary Tables

Temporary tables are session‑private; data disappears automatically at session end or transaction commit, depending on the ON COMMIT clause. They are created with CREATE GLOBAL TEMPORARY TABLE and can have indexes, views, or triggers.

Example of a transaction‑level GTT:

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

Verification of the table’s temporary status:

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

Session‑level isolation demo:

-- Session 1
INSERT INTO test VALUES (1, 'a');
SELECT * FROM test; -- returns the row
COMMIT;
SELECT * FROM test; -- no rows (transaction‑level)

-- Session 2
SELECT * FROM test; -- no rows (different session)

For a session‑level GTT:

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

Data persists for the duration of the session, even after commits, but disappears when the session ends.

Choosing the Best Approach

Option 1 offers the simplest, single‑pass retrieval if the result set size remains manageable and appropriate indexes exist. Option 2 avoids multiple SQL statements while still using indexes, but requires uniform column structures across child tables. Option 3 (temporary table) provides consistent index scans for each child table and eliminates the inefficiency of large IN lists, at the cost of an extra insert step and a temporary table lifecycle to manage.

In practice, the current implementation uses a split‑IN strategy (similar to Option 3’s third variant), breaking the ID list into chunks (e.g., 30 IDs per query) to retain index usage while limiting the number of statements.

Key Takeaways for Temporary Table Usage

Consider creating a dedicated temporary tablespace if the application performs many sorting or temporary‑table‑heavy operations to avoid contention.

When using session‑level GTTs with connection pooling, ensure that a single transaction does not span multiple pooled sessions, otherwise the temporary data may become inaccessible.

Overall, temporary tables are a straightforward tool for caching intermediate results, with only minor syntactic differences from regular tables, but they require attention to scope (session vs transaction) and resource isolation.