Re: 8i - Joining with Temporary Tables

Of course you can analyze them without loosing their contents if you created them with "on commit preserve rows":

drop table testtemp cascade constraints;

create global temporary table testtemp (n number) on commit preserve rows;

insert into testtemp (n) values (123);

analyze table testtemp compute statistics;

select * from testtemp;

will yield 123.

Martin

Jonathan Lewis 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.
>
> 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.
Received on Sun Jul 02 2000 - 00:00:00 CDT