8i - Joining with Temporary Tables

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. Received on Sat Jul 01 2000 - 00:00:00 CDT