Re: Temporary tables in memory

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 9 Sep 2003 19:49:41 +1000
Message-ID: <3f5da2a6$0$6528$afc38c87@news.optusnet.com.au>

"Hulse" <hulse_kevin_at_yahoo.com> wrote in message news:16926526.0309081335.5b298845_at_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.

The advantages of proper temporary tables (at least in Oracle -can't speak for Sybase) is that the contents of such tables are private to the instance that placed them there. Therefore there can be no possibility of you wanting to see rows that I'm working on; therefore there is no locking on them. That means the Oracle overhead for managing the data they contain is lower than it would be for an identical table kept in the keep pool.

Also, because the contents are temporary, they don't generate redo. They still generate undo, that's true. And the undo segment updates cause some redo to be produced, but nevertheless: the amount of redo an update on a temporary table causes should be much less than the amount of redo an identical 'proper' table would create. That means less log switches, less checkpoints, less archives etc etc. Again, much lower overhead.

They therefore have their uses, and it's a bit unfair to say that the same result could be achieved with regular tables and a keep pool.

Regards
HJR

>


> > >

> > > 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 Tue Sep 09 2003 - 04:49:41 CDT