Temporary table query wierdness

 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? Received on Tue Apr 09 2002 - 16:36:37 CDT