This is a multi-part request.

First, the system should support creation of temporary tables using a physical backing store rather than buffer manger. Given multi-pass SQL's heavy use of temp tables, buffer manager can easily be overloaded with large interim results stored in temp tables.

Second, this should be a user-configurable behavior. For example, user might be able to choose a system-level or session-level default from among:
– memory/cache
– a source represented by a connector binding
– a distinct temp source defined with it's own connection parameters (possibly another schema in the repository DB instance)
Ideally default selectoin should be override-able at temp table creation time through a DDL extension

In the case where multiple temp tables have been created on a source via connector, the query planner should recognize this and leverage pushdown to the temp store when later query passes access multiple temp tables.