Temporary tables that you create

You can create temporary tables with some SQL statements.

Temporary tables can be created with the following SQL statements:
  • TEMP TABLE option of the CREATE TABLE statement
  • INTO TEMP clause of the SELECT statement, such as SELECT * FROM customer INTO TEMP cust_temp

Only the session that creates a temporary table can use the table. When the session exits, the table is dropped automatically.

When you create a temporary table, the database server uses the following criteria:
  • If the query used to populate the TEMP table produces no rows, the database server creates an empty, unfragmented table.
  • If the rows that the query produces do not exceed 8 KB, the temporary table is located in only one dbspace.
  • If the rows exceed 8 KB, the database server creates multiple fragments and uses a round-robin fragmentation scheme to populate them unless you specify a fragmentation method and location for the table.
If you use the CREATE TEMP and SELECT...INTO TEMP SQL statements and DBSPACETEMP has been set:
  • LOGGING dbspaces in the list are used to create the tables that specify or imply the WITH LOG clause.
  • NON-LOGGING temporary dbspaces in the list are used to create the tables that specify the WITH NO LOG clause.

When CREATE TEMP and SELECT...INTO TEMP SQL statements are used and DBSPACETEMP has not been set or does not contain the correct type of dbspace, HCL Informix® uses the dbspace of the database to store the temporary table. See the Informix® Guide to SQL: Syntax for more information.