Re: Temporary tables in memory

From: Hulse <hulse_kevin_at_yahoo.com>
Date: 8 Sep 2003 14:35:56 -0700
Message-ID: <16926526.0309081335.5b298845@posting.google.com>

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0309021519.1dec21ff_at_posting.google.com>...

> chris_jack_at_msn.com (Chris Jack) wrote in message news:<648c9f62.0309020109.644fc60d_at_posting.google.com>...
> > "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<buG3b.295685$uu5.64729_at_sccrnsc04>...
> > > 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

[deletia]
> >
> > Sybase recommends (and makes it extremely easy) to cache its temporary
> > database in memory - but then Sybase development makes much heavier
> > use of temporary tables than Oracle does, so it is unfair to criticise
> > Oracle (too much) for something that is not a widely used feature.
> > Nevertheless for this style of coding, it means Oracle is an order of
> > magnitude slower than Sybase.
> >
> > To explain the Sybase to Oracle port in more detail, and why it
> > causing performance problems: if you want to populate a pop up list
> > box with all the currencies in the database, in Sybase you do:
> > select * from Currency
> >
> > in a stored procedure. To port this code to Oracle, tdhe vendor uses an
> > automated tool which does:
> > select * into CurrencyRS from Currency

      Based on this example, I am not even sure that there is any real point to bothering with temporary tables even in Sybase. If you need a small amount of data to be resident in memory, all you should really need do is to "pin" those tables into memory. I really don't get why one would have to use a special table type for this.

      In both Oracle & DB/2 there are auxillary block/page cache pools that you could pin a small lookup table into to ensure that table scans are done against physical memory.

      Based on this example, it sounds like you need to define a KEEP pool for the instance running this app, get rid of the temp table references generated by your code generator (sed, awk, perl), and set all of your little lookup tables to be in the KEEP pool.

> >
> > in PL/SQL and pushes the name CurrencyRS (a global temporary table) on
> > to a stack. The application then reads the table names off the stack
> > one by one and does a table scan on each table - cleaning out the
> > tables as it goes.
> >
> > Conceptually, this makes it easy to translate the Sybase code to
> > Oracle code and have one application handle both back ends (well you
> > have a different low level Oracle library, but the rest is the same).
> > The downside is it slows performance down by probably a factor of 20
> > (or maybe only 10 if logs weren't written for global temporary tables
> > - which, unfortunately does not appear to be the case).
Received on Mon Sep 08 2003 - 16:35:56 CDT