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.
[snip]

I found references to one solution, which is to set the table statistics (numrows, specifically) manually using DBMS_STATS.SET_TABLE_STATS. While this works, it appears that table statistics are shared among all instances. That is, even though every session gets its own copy of the data, there's only one copy of the stats. Two sessions with greatly differing rowcounts will step on each other's stats. Oh well.... I guess I'm going to have to go back to real tables.

GTTs don't seem to be fully baked yet, at least in 9i. Can anyone confirm that GTTs work better in 10g?

Jim Garrison
jhg_at_athensgroup.com Received on Thu Sep 23 2004 - 00:57:04 CEST