Jim Garrison wrote:


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 (DOT) com

Jonathan Lewis wrote:

That does the trick.

I added /*+ dynamic_sampling(gtt 1) */ and the query
works FASTER than a real table (about 15% faster)
and the execution plan is logical once again.

One note that should be in the docs is that if the
table has an alias in the statement then only the
alias name can be used in the hint. The full table
name is ignored in that case.

Thanks for your assistance.