Configuring Dbspaces for Temporary Tables and Sort Files

Applications that use temporary tables or large sort operations require a large amount of temporary space. To improve performance of these applications, use the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable to designate one or more dbspaces for temporary tables and sort files.

Depending on how the temporary space is created, the database server uses the following default locations for temporary table and sort files when you do not set DBSPACETEMP:

• The dbspace of the current database, when you create an explicit temporary table with the TEMP TABLE clause of the CREATE TABLE statement and do not specify a dbspace for the table either in the IN dbspace clause or in the FRAGMENT BY clause

  This action can severely affect I/O to that dbspace. If the root dbspace is mirrored, you encounter a slight double-write performance penalty for I/O to the temporary tables and sort files.

• The root dbspace when you create an explicit temporary table with the INTO TEMP option of the SELECT statement

  This action can severely affect I/O to the root dbspace. If the root dbspace is mirrored, you encounter a slight double-write performance penalty for I/O to the temporary tables and sort files.

• The operating-system directory or file that you specify in one of the following variables:
  • In UNIX, the operating-system directory or directories that the PSORT_DBTEMP environment variable specifies, if it is set

    If PSORT_DBTEMP is not set, the database server writes sort files to the operating-system file space in the /tmp directory.

  • In Windows, the directory specified in TEMP or TMP in the User Environment Variables window on Control Panel > System.

  The database server uses the operating-system directory or files to direct any overflow that results from the following database operations:

  • SELECT statement with GROUP BY clause
  • SELECT statement with ORDER BY clause
  • Hash-join operation
  • Nested-loop join operation
  • Index builds

You can improve performance with the use of temporary dbspaces that you create exclusively to store temporary tables and sort files. It is recommended that you use the DBSPACETEMP configuration parameter and the DBSPACETEMP environment variable to assign these tables and files to temporary dbspaces.

When you specify dbspaces in either the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable, you gain the following performance advantages:

• Reduced I/O impact on the root dbspace, production dbspaces, or operating-system files
• Use of parallel sorts into the temporary files (to process query clauses such as ORDER BY or GROUP BY, or to sort index keys when you execute CREATE INDEX) when you specify more than one dbspace for temporary tables and PDQ priority is set to greater than 0.
• Improved speed with which the database server creates temporary tables when you assign two or more temporary dbspaces on separate disks
• Use of parallel inserts into the temporary table when PDQ priority is set to greater than 0 and the temporary table is created with one of the following specifications that the following table shows.

  The database server automatically applies its parallel insert capability to fragment the temporary table across those dbspaces, using a round-robin distribution scheme.