Analyze temporary tables

[bensr]

Hi you all,
I have created some temporary tables, which contents are user-dependent.
I tried to analyze the table and did not get any errors,
but when examining the statistics I don't see any figures.
When selecting from those tables, the explain plan the optimizer stays on CHOOSE.
So, is it possible to compute statistics on temporary tables or doesn't it make any sence?

Who can tell something about it?

TIA

[Tarry]

Do you get statistics on others?
If not then you must set timed_statistics=true.

[bensr]

On ordinairy tables I do have statistic-figures and normally queries will execute CostBased.

[jmodic]

Analyzing temporary tables does not result in any ORA error, but on the other hand it doesn't collect any statistics either! So it is useless to analyze global temporary tables.

However the fact that CBO does not have any clue of how large temp table realy is might sometimes give it a big trouble in deciding the optimal execution path, resulting sometimes in very bad decision. But if you know that every user accesing that temporary table will use it the same way with approximately the same number of rows, you can fake the statistics for that table by using DBMS_STATS.SET_TABLE_STATS procedure, thus artificially importing your own "guessed" values for number of rows and blocks into the data dictionary, for example.

[bensr]

Thanks Jurij,
It helps.