Vlad,

> Separate page space seems most attractively from performance POV for

me.

For me either, but to making it useful is not that simple.

For example on Windows creating temporary files is horrifyingly slow and
also tends to fragment the filesystem, you can see this with on-disk
sorting module.
One big temporary space storage has better performance properties, but
it needs management and is not much better than normal database in that
regard.

Also, collapsing per-attachment temporary space is going to introduce
difficult issues which are going to translate to bad performance.
Imagine you create a temporary blob, which is the normal way you go to
assign values to blob fields.

When you create it you don't know which table it is going to be attached
to. You may pass blob to view or stored procedure and don't know the
destination table at all. What tablespace should engine use to store
this blob?

> > 2) Nickolay's ATTACHMENT_ID idea to add a hidden column to
> both data
> > and indices and teach the optimizer to filter the rows.
>
> I think this is not so good. It's seems to be easy to
> implement, but i showed even more easiest way.

Your approach is not easier, at least indices code would have to be
revamped and also would require special threatment of temporary tables
in GC and sweep handlers.

> But this has one big disadvantage - performance. Clean up at startup

is

> fast but we don't want to reload engine just to empty temp
> tables ;) Regular clean up (sweep, garbage collection) is
> slow for temp tables and must be avoided, imho.

We do GC for normal tables anyways, temp tables are not going to change
the picture significantly.
They are not going to be slower than normal tables and we'll have
motivation to optimize normal access paths, not one special case of
temporary tables.
BTW, note that due to multiple transactions allowed for one connection
update conflicts are possible for temporary tables and the most of the
versioning, conflict resolution and GC logic of CCH, DPM and VIO modules
still applies.

> And i can't see how Nickolay idea satisfy tables with ON
> COMMIT DELETE option.

Do what's asked. Delete rows from temp table in DFW handler at commit
time.
We are going to implement deferred constraints so this is not going to
change the big performance picture either.

> At last, different page space allows read-only database
> to work with temp data and still remains read-only - this can
> be important for some applications

For those special applications NBACKUP allows to have read-only main
database file and redirect writes to separate read-write storage. :-)

The benefit of my approach is that it would allow to share 99% of
implementation with normal tables motivating us to optimize caching
logic, data placement logic and access paths for entire engine, not only
for temporary tables.

> Vlad

Nickolay