Re: Temporary table query wierdness

It's a known issue, which is in the book. ANALYZE will not write stats to a temp
table, so the probability of the path being different when using a proper table with stats is high.

One option is to use dbms_stats to write 'representative' stats to the tab$ definition of the temp table.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Lee M Horowitz wrote in message ...


>

> I was playing around with temporary tables .....Create global

>temporary table (col1 type, ....,  coln type) ON COMMIT preserve rows

>....

>I had a join to a large real table. If I use the same "shape" real

>table (same shape as the temp table, I mean), I get reasonably good

>performance, but when I change nothing but the name of the driving

>table (real table in one case, temp table in the othe) data

>same,indexes the same, colum names and types the same, all same-same

>then for some reason using the temp table slows the query down big

>time. Change the table name back to the "real" table, wham-o! Fast

>again.

>

>Clearly the optimizer is doing something differently for the temp

>table, (I didnt get an explain plan for it, duh!) but clearly the

>plans must be different. Does anyone have  a clue as to what Oracle

>must be thinking?

>

>Come to think of it, how do temp tables work with cost based

>optimization? Can you "analyze" a temp table? What would that mean,

>since the data is presumably dumped at every new session?

>

>Is there a cost-base/rule-based funny going on here?

>

>