Temporary Tables

The two types of temporary tables are explicit temporary tables and implicit temporary tables. You can create temporary tables in a standard dbspace or temporary dbspace.

An explicit temporary table is a temporary table that you create with one of the following SQL statements:

• TEMP TABLE option of the CREATE TABLE statement
• INTO TEMP clause of the SELECT statement

  SELECT * FROM customer INTO TEMP temp_table

• SCRATCH TABLE option of the CREATE TABLE statement
• INTO SCRATCH clause of the SELECT statement

When an application creates an explicit temporary table, it exists until the application takes one of the following actions:

• The application terminates.
• The application closes the database in which the table was created and opens a database in a different database server.
• The application closes the database in which the table was created.

When any of these three events occurs, the database server deletes the temporary table.

An implicit temporary table is a temporary table or file that the database server creates as part of processing.

The following statements might require temporary disk space:

• 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
• DECLARE statements that use the SCROLL CURSOR option

The database server deletes an implicit temporary table when the processing that initiated the creation of the table is complete.

If the database server shuts down without adequate time to clean up temporary tables, it performs temporary table cleanup as part of the next initialization. (To request shared-memory initialization without temporary table cleanup, execute oninit with the -p option.)

Storage of Temporary Tables

Adequate temporary space in the appropriate dbspaces to store temporary tables and files is critical to the overall performance of your database server. The output file of the SQL statement SET EXPLAIN ON lists temporary-file requirements.

The dbspace in which the database server stores temporary tables depends on whether the table is an explicit or implicit table. The following sections examine both cases in detail.

Explicit Temporary Tables

When you create an explicit temporary table using the IN dbspace option of CREATE TEMP TABLE, the database server stores the temporary table in that dbspace.

When you do not use the IN dbspace option of CREATE TEMP TABLE, or when you create the explicit table with SELECT... INTO TEMP, the database server checks the DBSPACETEMP environment variable and the DBSPACETEMP configuration parameter. (The environment variable supersedes the configuration parameter.) When DBSPACETEMP is set, the database server stores the explicit temporary table in one of the dbspaces specified in the list.

The database server keeps track of the last dbspace in the list that it used to store a temporary table. When the database server receives another request for temporary storage space, it uses the next dbspace in the list. In this way, the database server spreads I/O evenly across the temporary storage space that you specify in DBSPACETEMP.

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 (nontemporary) 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.
• The default value for the DBSPACETEMP configuration parameter in Extended Parallel Server is NOTCRITICAL. Therefore, explicit temporary tables do not use the root dbspace in Extended Parallel Server. For more information on temporary tables, refer to Temporary Tables.

Flexible Temporary Tables

A flexible (flex) temporary table is an explicit temporary table that the database server creates and then fragments (round-robin method) automatically. The following query, for example, creates a flex temporary table:

SELECT * FROM customer INTO SCRATCH temp_table

One advantage of a flex temporary table over a table created with CREATE TABLE syntax is that you do not need to know column names and data types.

Extended Parallel Server uses an SQL operator to optimize use of dbspaces and dbslices for temporary storage. When data is received, a fragment of that table is created in one of the available dbspaces (as determined by the value of DBSPACETEMP), and data is light-appended to the fragment. If the dbspace that is being used becomes full, the SQL operator attempts to write incoming data into another dbspace on the same node. For more information on fragmenting a flex temporary table, refer to the IBM Informix: Performance Guide.

Implicit Temporary Tables

The database server stores implicit temporary tables in one of the dbspaces that you specify in the DBSPACETEMP environment variable or the DBSPACETEMP configuration parameter. The environment variable supersedes the configuration parameter.

When the DBSPACETEMP environment variable and the DBSPACETEMP configuration parameter are not set, Extended Parallel Server stores the temporary table in the default dbspaces or dbslices specified in the DBSPACETEMP configuration parameter. The default value of DBSPACETEMP in onconfig.std or onconfig.xps is NOTCRITICAL, which includes all standard dbspaces or dbslices except the root or logs.

For information on how to create temporary dbspaces, refer to Creating a Temporary Dbspace.