Temporary Tables

The database server needs to provide disk space for temporary tables of the following two kinds:

• Temporary tables that you create with an SQL statement, such as CREATE TEMP TABLE... or SELECT .... INTO SCRATCH
• Temporary tables that the database server creates as it processes a query

Make sure that your database server has configured enough temporary space for both user-created and database server-created temporary tables. Some uses of the database server might require as much temporary storage space as permanent storage space, or more.

By default, the database server stores temporary tables in the root dbspace. If you decide not to store your temporary tables in the root dbspace, use the DBSPACETEMP environment variable or the DBSPACETEMP configuration parameter to specify a list of dbspaces for temporary tables.

Temporary Tables That You Create

You can create temporary tables with any of 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 kilobytes, the temporary table resides in only one dbspace.
• If the rows exceed 8 kilobytes, 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, Dynamic Server uses the dbspace of the database to store the temporary table. See the IBM Informix Guide to SQL: Syntax for more information.

Where User-Created Temporary Tables are Stored

If your application lets you specify the location of a temporary table, you can specify either logging spaces or nonlogging spaces that you create exclusively for temporary tables. SCRATCH tables are not logged and must be created in temporary spaces.

For information about creating temporary dbspaces and dbslices, refer to the onspaces section in t.

If you do not specify the location of a temporary table, the database server stores the temporary table in one of the spaces that you specify as an argument to the DBSPACETEMP configuration parameter or environment variable. The database server keeps track of the name of the last dbspace that it used for a temporary table. When the database server receives another request for temporary storage space, it uses the next available dbspace to spread I/O evenly across the temporary storage space.

For information about where the database stores temporary tables when you do not list any spaces as an argument to DBSPACETEMP, see the DBSPACETEMP section in the IBM Informix Administrator's Reference.

When you use an application to create a temporary table, you can use the temporary table until the application exits or performs one of the following actions:

• Closes the database in which the table was created and opens a database in a different database server
• Closes the database in which the table was created
• Explicitly drops the temporary table

Temporary Tables That the Database Server Creates

The database server sometimes creates temporary tables while running queries against the database or backing it up. The database server might create a temporary table in any of the following circumstances:

• Statements that include a GROUP BY or ORDER BY clause
• Statements that use aggregate functions with the UNIQUE or DISTINCT keywords
• SELECT statements that use auto-index or hash joins
• Complex CREATE VIEW statements
• DECLARE statements that create a scroll cursor
• Statements that contain correlated subqueries
• Statements that contain subqueries that occur within an IN or ANY clause
• CREATE INDEX statements

When the process that initiated the creation of the table is complete, the database server deletes the temporary tables that it creates.

If the database server shuts down without removing temporary tables, it performs temporary table cleanup the next time shared-memory is set up. To initialize shared memory without temporary table cleanup, execute oninit with the -p option.

Where Database Server-Created Temporary Tables are Stored

When the database server creates a temporary table, it stores the temporary table in one of the dbspaces that you specify in the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable. The environment variable supersedes the configuration parameter.

When you do not specify any temporary dbspaces in DBSPACETEMP, or the temporary dbspaces that you specify have insufficient space, the database server creates the table in a standard dbspace according to the following rules:

• If you created the temporary table with CREATE TEMP TABLE, the database server stores this table in the dbspace that contains the database to which the table belongs.
• If you created the temporary table with the INTO TEMP option of the SELECT statement, the database server stores this table in the root dbspace.

For more information, see Creating a Temporary Dbspace.