Re: Guideline on use of temporary tables

On 10/12/07, Jimmy Choi <yhjchoi(at)gmail(dot)com> wrote:
> I'm looking for general guideline on the use of temporary tables.
>
> I would like to use temporary table as a caching mechanism to speed up
> queries within the same session. Specifically, a temporary table is
> created to store a subset of data from a possibly large table, and
> subsequent queries select from the temporary table instead of
> re-applying the same complex filters on the actual table again and
> again.
>
> Is this what temporary table is designed for? Are there caveats that I
> should be aware of? Can you think of other better alternatives?

well, let's start with listing a couple of reasons reasons _not_ to
use temporary tables.
* your application sessions can't be mapped to database sessions
(usually web environments with connection pooling)
* plan invalidation issues with functions (largely fixed in upcoming 8.3)
* you need tables to be 'global'...shared between sessions
* you are using temp tables in place of a more elegant solution like a
view (this is very situational)

other than the above, go for it; temp tables are faster than regular
tables and give you full benefits of sql for holding and manipulating
your data.

merlin