Re: Global temporary tables v pl/sql tables

In article <zMtW8.31045$Hj3.94011_at_newsfeeds.bigpond.com>, you said (and I quote):
>
> "Ryan" <rgaffuri_at_cox.net> wrote in message
> news:cpoW8.68471$DB.1742984_at_news1.east.cox.net...
> > What about performance comparisons? I know that in some cases pl/sql
> tables
> > are useful if you need to do a series of joins and /or sorts you can load
> > one table into memory by putting it in a pl/sql table.
>
> Exactly. The data is all stored in memory and hence can be reaccessed very
> effeciently. The issue of course is do you have sufficient memory available.
> Or would it be useful to access this data multiple times from multiple
> pieces or executions of code, etc. Then a GTT could be useful.
>

A minor word of caution here (this is a V7 and V8.0 observation, might be different with 8i or 9i compiled PL/SQL).

Assuming you have a normal block of code where a main cursor executes a basic SELECT that is going to provide rows processed in a tight loop:

• Be aware that a linear scan of an in-memory PL/SQL table does actually use a finite amount of CPU, proportional to the number of "rows" in it.
• If you can load a normal table with your lookup data, index it and then join it with the data you're processing, you'll find that this will be faster as soon as the number of rows in the table is > than about 500.
• If the number of rows is lower than that, then it's faster to load the table as a PL/SQL table, linearly scan it and do the join "by hand".
• Of course, if you feel that you can write your own binary search routine to scan the in-memory table based on a hashed key, then you may well find that the 500 "magic" number is actually more like 50000.

-Always use a PLS_INTEGER typed variable to index and point to the inmemory  table: they are so much faster than NUMBER for integer operations it's not even funny...

Of course: try this with your own settings and version: it'd be highly dependent on Oracle version, I'd say.

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam