Re: Temporary tables in memory

One opinion/thought with some questions.... further testing needed...

Global tempoary tables are stored in the temporary tablespace, but I see your point in that you assume this is disk I/O...

I have looked at the docs and it is not clear if a global temporary table would use the sort_area_size memory area of a session if it were large enough?

Since other operations use the sort_area_size of memory before going to the temporary tablespace, it would be nice if this worked that way too.

Then, assuming you have the memory, you could just allocate more memory to each session's sort_area_size .

-- 
"Chris Jack" <chris_jack_at_msn.com> wrote in message
news:648c9f62.0308280512.38a39b62_at_posting.google.com...


> Is there a way in Oracle 8i (or later, but would prefer 8i solution)

> of avoiding the writing to disk of temporary tables: i.e. tables

> created with 'create global temporary table'...? Alternatively, can

> someone confirm it is not possible so I can stop worrying about it?

>

> For instance, it seems to me you are required to store temporary

> tables in a tablespace, and a tablespace must be stored on disk. It

> appears that that disk cannot be a memory mapped device - but maybe

> someone knows a way around this.

>

> I have a rule of thumb that, in the absence of other information,

> disks are nine times as slow as memory. As the application makes

> significant use of temporary tables, the overhead is significant. I

> cannot do anything about the application and if I told you it was a

> Sybase to Oracle port, you might guess at why the temporary tables are

> used.

>

> Also, do temporary tables get logged and, if so, is there a way of

> turning this off?

>

> Thanks in advance

> Chris