Re: Global temporary tables

Hi

> There is one more problem with global temporary tables for which I do not
> know good solution now: collecting statistic.
> As far as each backend has its own data, generally them may need different
> query plans.
> Right now if you perform "analyze table" in one backend, then it will
> affect plans in all backends.
> It can be considered not as bug, but as feature if we assume that
> distribution if data in all backens is similar.
> But if this assumption is not true, then it can be a problem.
>

Last point is probably the most difficult issue and I think about it years.

I have a experience with my customers so 99% of usage temp tables is
without statistics - just with good information only about rows. Only few
customers know so manual ANALYZE is necessary for temp tables (when it is
really necessary).

Sharing meta data about global temporary tables can real problem - probably
not about statistics, but surely about number of pages and number of rows.

There are two requirements:

a) we need some special meta data for any instance (per session) of global
temporary table (row, pages, statistics, maybe multicolumn statistics, ...)

b) we would not to use persistent global catalogue (against catalogue
bloating)

I see two possible solution:

1. hold these data only in memory in special buffers

2. hold these data in global temporary tables - it is similar to normal
tables - we can use global temp tables for metadata like classic persistent
tables are used for metadata of classic persistent tables. Next syscache
can be enhanced to work with union of two system tables.

I prefer @2 because changes can be implemented on deeper level.

Sharing metadata for global temp tables (current state if I understand
well) is good enough for develop stage, but It is hard to expect so it can
work generally in production environment.

Regards

p.s. I am very happy so you are working on this topic. It is interesting
and important problem.

Pavel

>
>
>
>