... |
* "drop behavior" option is not supported in the drop statement. |
* Only local temporary tables are supported. This implies that the scope of temp table will be either to the session or the block of a virtual procedure that creates it. |
* Only local temporary tables are supported. Teiid interpretation of local is different than the SQL specification and other database vendors. Local means that the scope of temp table will be either to the session or the block of a virtual procedure that creates it. Upon exiting the block or the termination of the session the table is dropped. Session and any other temporary tables created in calling procedures are not visible to called procedures. If a temporary table of the same name is created in a called procedure a new instance is created. |
* Session level temp tables are not fail\-over safe. |
... |
Teiid supports creating temporary,or "temp", tables. Temp tables are dynamically created, but are treated as any other physical table.
Temp tables can be defined implicitly by referencing them in a INSERT statement or explicitly with a CREATE TABLE statement. Implicitly created temp tables must have a name that starts with '#'.
Creation syntax:
Explicit:
CREATE LOCAL TEMPORARY TABLE name (column type [NOT NULL], ... [PRIMARY KEY (column, ...)])
Implicit:
INSERT INTO #name (column, ...) VALUES (value, ...)
If #x doesn't exist, it will be defined using the given column names and types from the value expressions.
Implicit:
INSERT INTO #name [(column, ...)] select c1, c2 from t
If #x doesn't exist, it will be defined using the target column names (in not supplied, the column names will match the derived column names from the query), and the types from the query derived columns.
Drop syntax:
DROP TABLE name
Primary Key Support
Limitations:
The following example is a series of statements that loads a temporary table with data from 2 sources, and with a manually inserted record, and then uses that temp table in a subsequent query.
... CREATE LOCAL TEMPORARY TABLE TEMP (a integer, b integer, c integer); SELECT * INTO temp FROM Src1; SELECT * INTO temp FROM Src2; INSERT INTO temp VALUES (1,2,3); SELECT a,b,c FROM Src3, temp WHERE Src3.a = temp.b; ...
See Virtual Procedures for more on temp table usage.
Unlike a Teiid local temporary table, a foreign temporary table is a reference to an actual source table that is created at runtime rather than during the metadata load.
A foreign temporary table requires explicit creation syntax:
CREATE FOREIGN TEMPORARY TABLE name ... ON schema
Where the table creation body syntax is the same as a standard CREATE FOREIGN TABLE DDL statement. In general usage of DDL OPTION clauses may be required to properly access the source table, including setting the name in source, updatability, native types, etc.
The schema name must specify an existing schema/model in the VDB. The table will be accessed as if it is on that source, however within Teiid the temporary table will still be scoped the same as a non-foreign temporary table. This means that the foreign temporary table will not belong to a Teiid schema and will be scoped to the session or procedure block where created.
The DROP syntax for a foreign temporary table is the same as for a non-foreign temporary table.
Neither a CREATE nor a corresponding DROP of a foreign temporary table issue a pushdown command, rather this mechanism simply exposes a source table for use within Teiid on a temporary basis.
There are two usage scenarios for a FOREIGN TEMPORARY TABLE. The first is to dynamically access additional tables on the source. The other is to replace the usage of a Teiid local temporary table for performance reasons. The usage pattern for the latter case would look like:
//- create the source table source.native("CREATE GLOBAL TEMPORARY TABLE name IF NOT EXISTS ON COMMIT DELETE ROWS"); //- bring the table into Teiid CREATE FOREIGN TEMPORARY TABLE name ... OPTIONS (UPDATABLE true) //- use the table ... //- forget the table DROP TABLE name
Note the usage of the native procedure to pass source specific CREATE ddl to the source. Teiid does not currently attempt to pushdown a source creation of a temporary table based upon the CREATE statement. Some other mechanism, such as the native procedure shown above, must be used to first create the table. Also note the table is explicitly marked as updatable, since DDL defined tables are not updatable by default.
The source's handling of temporary tables must also be understood to make this work as intended. Sources that use the same GLOBAL table definition for all sessions while scoping the data to be session specific (such as Oracle) or sources that support session scoped temporary tables (such as PostgreSQL) will work if accessed under a transaction. A transaction is necessary because:
Since Teiid does not yet support the ON COMMIT clause it's important to consider that the source table ON COMMIT behavior will likely be different that the default, PRESERVE ROWS, for Teiid local temporary tables. |