On 30 Nov 2004 at 13:13, Ann W. Harrison wrote:

> why do we need temporary tables at all? Why not use
> a conventional table with fields defined to contain
> the connection identifier and the current date? Those
> two fields can be filled in with triggers. Create a
> view that excludes the connection and date and filters
> on the connection, and you've got a temporary table,
> lacking only cleanup. Use a simple procedure that
> filters on the date field from time to time to remove
> data more than two weeks old.
>
> Not perfect, certainly, but free, and doesn't involve
> mucking with internals at all.

Well, that's how people implement it today :-)

There are two reasons for "magic" temporary tables, and both come down to
performance:

1. . Connection id would very likely have very bad cardinality, so filter
performance would be bad even with index on it.

2. Data cleanup. It would have the same problems of large deletes like in
normal table. With separate table it's easy to scrap all data pages which
is quick and cheap. BTW, there is a feature request pending for EMPTY or
PURGE/DELETE ALL command that would quickly remove all data from table.

Best regards
Pavel Cisar (ICQ: 89017288)
http://www.ibphoenix.com
For all your upto date Firebird and
InterBase information