HP 3000 Manuals HP 3000 Manuals
Calculating Temporary Disk Space
ALLBASE/SQL uses temporary disk space of two kinds for sorting and other
operations:
* Space in temporary operating system files
* Space in the SYSTEM DBEFileSet
Queries that contain the following clauses use temporary pages in
operating system files to store temporary information:
* DISTINCT
* GROUP BY
* ORDER BY
* UNION (but not UNION ALL)
In addition, the following operations also use temporary table space in
the SYSTEM DBEFileSet to store sorted data:
* Queries that use sort/merge join processing.
* The CREATE INDEX statement, when it operates on tables containing
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.