Calculating Temporary Disk Space

ALLBASE/SQL uses temporary disk space of two kinds for sorting and other operations:

Queries that contain the following clauses use temporary pages in operating system files to store temporary information:

In addition, the following operations also use temporary table space in the SYSTEM DBEFileSet to store sorted data:

Sorting normally requires about twice the space occupied by the result table being sorted. Three times the space is required for sorting the result of a SELECT that employs a defined key field or that joins two or more tables and uses an ORDER BY clause. Space for sorting is taken from the tempspace available on the system. Temporary files are created in the tempspace, and they are removed after the query is processed. Space is used in the SYSTEM DBEFileSet whenever a temporary table is created as a result of sorting or query processing. The SYSTEM DBEFileSet must be large enough to store these temporary tables.

ALLBASE/SQL will use space in the current group if you do not specify any other location for temporary space. If this area is not adequate for your needs, you can use the CREATE TEMPSPACE statement to define a different area, which then serves as the location for creating temporary files when they are needed for sorting.

The default number of pages used per file for tempspace is 256. The total amount of space used is all that is available in the tempspace area, which on MPE/iX is the current group.

The following is an example of a statement that defines an existing MPE group as a tempspace:

   CREATE TEMPSPACE Temp1 WITH MAXFILEPAGES=400, LOCATION= 'SomeGrp.SomeAcct';

The group SomeGrp.SomeAcct must exist already. After you issue this statement, the group SomeGrp.SomeAcct becomes available for the creation of one or more temporary files, each of which can be up to 400 pages in size.

Many files may be opened in one tempspace, and each may be expanded up to 400 pages. The number 400 is an arbitrary value chosen for this example. See the "CREATE TEMPSPACE" section in the ALLBASE/SQL Reference Manual for the range of allowed values.

Note that the CREATE TEMPSPACE statement does not actually create the group SomeGrp.SomeAcct; it merely stores the description of the TempSpace in the system catalog table SYSTEM.TEMPSPACE. The actual temporary files for sorting are created at the time the sort is carried out, and they are dropped when the sort is finished. If you want to remove the TempSpace definition from the system catalog, use the DROP TEMPSPACE statement.

Controlling the Use of Temporary Space

The order in which you carry out database operations may determine the amount of temporary space required. For example, the following two scenarios use the same number of pages at the end of the operation, but require different amounts of temporary space because the CREATE INDEX statement calls the same sort routine for an empty table as for a non-empty table:

(1) CREATE TABLE CREATE INDEX INSERT 20000 rows

Scenario 1 does not require the use of temporary space, since the table is never sorted. Instead, the index is updated as a part of each INSERT operation.

(2) CREATE TABLE INSERT 20000 rows CREATE INDEX

Scenario 2 uses temporary files for sorting, and it also creates a temporary relation to hold the sorted table prior to the creation of the index.