Re: 8i - Joining with Temporary Tables

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.

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

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.

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

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Dylan Kucera wrote in message
<840362F4D9AB4C2D.0A6D55E3AF48BC6A.909DF332077DF2E8_at_lp.airnews.net>...


>I'm currently experimenting with migrating a database/application from MS

 SQL


>Server 7 to Oracle 8.1.6.  I have used the Migration Workbench quite

>successfully to migrate tables and procedures, at least syntactically.

>

>In traditional SQL Server style, temporary tables are used heavily within

 the


>stored procedures.  The problems I'm experiencing are illustrated well by a

>query that joins 7 tables (!ouch I know, bear with me), 4 are temporary

 tables


>and 3 are large permanent tables.

>

>Before I continue, I know many will take exception to my use of temporary

>tables at all, and especially to the fact that I'm joining so many tables.

>Please be sympathetic to the fact that this is what I have to work with, to

>rewrite the entire application is not possible, there is much of this sort

 of


>work going on throughout.  Let it be known that the temporary tables are

 all


>very small (< 10 rows) and the permanent tables are well indexed for the

>joins.

>

>When I insert 1 record into each of the 4 temporary tables and run the

 query,


>using either the rule or cost optimizer plan the query takes over 5 minutes

 to


>run.

>

>When I create 4 permanent tables that look exactly like the temporary

 tables


>in structure, insert 1 row into each, analyze table each of them then run

 the


>query, the query returns almost instantly with the results.

>

>I cannot run analyze table on the temporary tables, and as a result when I

>view the plan it appears that the optimizer is expecting 8168 rows from the

>temporary tables.  The plan that it builds on this expectation appears to

 be


>rather poor.  In practise, the temporary tables in this application would

 not


>contain more than a few rows each.

>

>I am tempted to re-run the Migration Workbench with "8i Temporary Table"

>support turned off and see what I get (I believe it will use permanent

 tables


>with a session id key of some sort, then maybe I can run an analyze on

 them?).


>I thought I'd check if anyone here could think of something to try before I

>abandon trying to use 8i temporary tables in joins.

>

>Thanks very much for any follow ups or e-mails!

>DK.