GLOBAL TEMPORARY table - serious join performance problem

1. Create a GLOBAL TEMPORARY table and populate it with one (1) row.
2. Join that table to another with about 1 million rows. The join condition selects a few hundred rows.

Performance: 4 seconds, the system is doing a full-table scan of the second table, and the Explain Plan output indicates that Oracle thinks the first table has 4000 rows.

Now replace the GLOBAL TEMPORARY table with a real table and repeat exactly the same query. This runs in 94 milliseconds and the Explain Plan shows the correct number of rows for the driving table and an index scan on the second table, as I would have expected.

Can anyone suggest a solution that will make the GLOBAL TEMPORARY implementation as fast as the real table version?

BTW, why are there two sets of parallel groups under both comp.database.oracle and comp.databases.oracle?

Jim Garrison
jhg_at_athensgroup.com Received on Thu Sep 23 2004 - 00:06:33 CEST