Re: Temporary tables in memory

> Stop using temp tables and just go after the data directly. AFAIK, the
> tables are not logged.Are you having a performance problem or are you just
> worrying about it.
> Jim

I would stop using temporary tables if I could, but I am not at the right point in the development feeding chain to do this - there is a compiled application that is expecting these temporary tables at the moment and I don't have the source code to change the application.

It is causing performance problems, but if I cannot do anything about it - then I don't have to worry about thinking of a solution.

Sybase returns result sets directly by just issuing queries from a stored procedure - PL/SQL uses other mechanisms like ref cursors. The problem arises because the method used to port the Sybase queries to Oracle was to store the results in a temporary table.

There is a later version of the product due out sometime that is replacing the temporary result set tables by ref cursors for the core product (although a few returned result sets will still require temporary tables for technical reasons) however...

2 clients will not be upgrading for some time and there are current performance problems which it would be politically extremely nice to solve immediately. Also, some peripheral, but extremely important, parts of the product will not be taking of advantage of the ref cursor change immediately.

So if there was a configuration fix, it would save us a lot of aggrevation.

I am still learning Oracle and the Oracle way of doing things so it is unfair for me to be disparaging about Oracle (and on a forum like this I am sure I would get flamed to pieces).

So putting my head in the lions jaw... Sybase is typically configured to store its temporary database (Oracle would probably call this a tablespace) in memory. It gets away with this by recreating the temporary database from scratch every time the server is restarted.

I suppose Oracle has never seen a need to do this because Oracle people don't, by and large, use temporary tables.

Regards
Chris Received on Fri Aug 29 2003 - 04:05:12 CDT