Re: Oracle replacement for Sybase #temp tables

In article <84t2aq$q3j$1_at_jetsam.uits.indiana.edu>,   kallbac_at_steel.ucs.indiana.edu (Kristy Kallback-Rose) wrote:
> We are in the process of converting to Oracle from Sybase. Under
Sybase we
> had used #temp tables quite a bit (within SQRs, and in the SQL for
> datagroup builds to avoid complex joins, for example) and need a
> replacement for them in Oracle.
>
> Oracle's global temporary tables aren't quite ready for prime-time
yet, in
> that they have some optimizer problems. I don't want to start a
discussion
> about GTTs, we've already decided not to use them. Rather I'm hoping
to
> hear from someone who used Sybases's #temp tables and is now using,
for
> lack of a better term, 'permanent temporary tables' in Oracle with
> something like sessionid key to keep the table name unique. It is a
> requirement, at least for the SQRs, that the table name be unique.
>
> If someone does have this type of experience behind them I'd be glad
to
> provide more details. You can reply to this post or via e-mail.
>
> Thanks,
> Kristy
> --
> Kristy Kallback-Rose
> Information Access & Repository Services Team
> University Information Technology Services
> Indiana University, Bloomington
>

Kristy,

Have you looked into not using temp tables at all and just writing complex queries through ORACLE. On prior projects, we did some timings on processes using temp tables vs complex SQL. Complex SQL was for the most part(although not always) faster than the temp tables. The I/O to write the temp table, and then read it back is very expensive. Just a thought.

thanks,

dave

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 13:44:01 CST