Re: 8i - Joining with Temporary Tables

In article <962472286.9559.0.nnrp-08.9e984b29_at_news.demon.co.uk>, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>Unfortunately you have hit the very point
>that makes temporary tables a bit of a pain.
>You cannot analyze them, so Oracle can
>generate some totally atrocious plans when
>you mix temporary and permanent tables.

Thanks very much for your reply, and helping me to confirm that I'm not just missing something.

>Are you running with FIRST_ROW or ALL_ROWS ?
>If ALL_ROWS, you may find that FIRST_ROWS
>works better anyway.

The optimizer seems to choose ALL_ROWS as the lower cost which is the "5 minute" statistic that I gave. I tried forcing FIRST_ROWS, which does do considerably better at about 2.5 minutes, but still not good enough. Sadly when I use all permanant analyzed tables, Oracle eats SQL Server for lunch.

>One option (but don't take this at all seriously)
>is to create you own indexing types using the
>extensibility features - analyze of temporary
>tables will then actually record your own defined
>stats.

I did read something in the documentation about that. Unfortunately I don't believe I have enough experience with Oracle to successfully do this given that the documentation on these sorts of features does seem to be targeted at the experienced and advanced user. All I could really make of it was that it would be difficult to learn how to do, then very arduous to add such indices to all temporary tables.

>The only other option is to HINT the SQL
>that mixes temporary table permanent tables
>in the most generic way possible.

I tried numerous hints but was unable to get the optimizer to choose the correct path. I somewhat weakly concluded that each query would need much analysis and a custom tailored hint, not really an option.

Thanks again, unless someone else has something worth trying, I think I'll move to plan B and try permanent tables with session ID's (that is if the Migration Workbench does infact do that with the 8i temp tables feature turned off).

DK. Received on Sat Jul 01 2000 - 00:00:00 CDT