| SQL adaptation guide for Oracle Database 11, 12 / Data manipulation | |
Informix® temporary tables are created through the CREATE TEMP TABLE DDL instruction or through a SELECT ... INTO TEMP statement. Temporary tables are automatically dropped when the SQL session ends, but they can also be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.
BDL reports create a temporary table when the rows are not sorted externally (by the source SQL statement).
Informix allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.
ORACLE does not support temporary tables as Informix does. ORACLE 8.1 provides GLOBAL TEMPORARY TABLEs which are shared among processes (only data is temporary and local to a SQL process). Informix does not shared temp tables among SQL processes; each process can create its own temp table without table name conflicts.
In accordance with some prerequisites, temporary tables creation in BDL programs can be supported by the database interface.
The temporary table emulation can use regular tables or GLOBAL TEMPORARY tables. The way the driver converts Informix temp table statements to Oracle regular tables or global temporary tables is driven by the following FGLPROFILE entry:
dbi.database.<dbname>.ifxemul.temptables.emulation = {"default" | "global" }
By default, the database driver uses regular tables (default emulation). This default emulation provides maximum compatibility with Informix temporary tables, but requires real table creation which can be a significant overhead with Oracle. The global emulation uses native Oracle Global Temporary Tables, requiring only one initial table creation and thus making programs run faster. However, the global emulation mode has to be used carefully because of some limitations and constraints.
When creating a temporary table, you perform a Data Definition Language statement. Oracle automatically commits the current transaction when executing a DDL statement. Therefore, you must avoid temp table creation/destruction in transactions.