Re: 8i - Joining with Temporary Tables

In article <395F3946.D06F02F3_at_0800-einwahl.de>,   Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> wrote:
> 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.
>

but to continue the example on:

ops$tkyte_at_8i> select num_rows from user_tables where table_name = 'TESTTEMP';   NUM_ROWS

ops$tkyte_at_8i> create table realtable ( n number );

Table created.

ops$tkyte_at_8i> insert into realtable values (123);

1 row created.

ops$tkyte_at_8i> analyze table realtable compute statistics;

Table analyzed.

ops$tkyte_at_8i> select num_rows from user_tables where table_name = 'REALTABLE';   NUM_ROWS

         1

ops$tkyte_at_8i>

it did not really anlayze it -- num_rows is still NULL for the temp table...

> 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.
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.