"Chris Jack" <chris_jack_at_msn.com> wrote in message
news:648c9f62.0308290105.63915544_at_posting.google.com...
> > 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
How does the vendor solve the problem? Can you use global temporary tables?
Oracle does allow you to have a temporary tablespace that is a temp file.
(similar to Sybase and an efficient way to do it)
Jim
Received on Fri Aug 29 2003 - 06:07:51 CDT
