Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> GLOBAL TEMPORARY table - serious join performance problem

GLOBAL TEMPORARY table - serious join performance problem

From: Jim Garrison <jhg_at_athensgroup.com>
Date: Wed, 22 Sep 2004 17:06:33 -0500
Message-ID: <GfednS5GOq_UaszcRVn-iA@giganews.com>


Scenario:

  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 Wed Sep 22 2004 - 17:06:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US