Re: 8i - Joining with Temporary Tables

>You just gave me an idea. what about dbms_stats? we can analyze the
>table (but that doesn't have any effect -- it'll come back with NULL
>num_rows and such) but we can write the stats we believe will be true
>into the DD using dbms_stats. The following:

..snip...

>Perhaps Dylan (i've cc'ed him) can try this out and let us know. He
>can set the number of rows to what he believes is the correct amount
>using dbms_stats after creating the tempporary table and the CBO should
>have a better go of it.

Canada Day weekend or not, you guys are so great for helping me out, I went in to the office to try this out.

Well, it was worth it. I did the dbms_stats.set_table_stats on my four temporary tables, then ran my monster join. The optimizer picked an appropriate plan and returned with the results almost instantly.

As per my original description, most of my temporary tables will contain very few records: much of the time 1, some of the time 10, and occationally 100. A temporary table in my application should always fall into one of those 3 buckets, so I expect to be able to set_table_stats once and be done with it.  I am a little confused about the parameters though, I used 1, 1, 256 for numrows, numblks and avgrlen respectively. Can you suggest a more appropriate set of numbers? Can you help me with the last 2 parameters in the case of the 100 record temporary table?

Tom, feel free to post something about this on your web page if you think it's important enough. I hope someone at Oracle will read some of this and consider improving the optimizer's treatment of temporary tables.

Thanks very much!!!
DK. Received on Mon Jul 03 2000 - 00:00:00 CDT