Re: GLOBAL TEMPORARY table - serious join performance problem

Jim Garrison wrote:

> 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

Which type of GTT? There are two and they behave very differently.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)