SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
T-4
Temporary Tables
Temporary Tables
SQL creates temporary tables as needed according to these rules:

If a query involves a join operation, SQL creates a temporary table on the volume
on which the outermost table in the join resides. If the table is partitioned, SQL
creates a temporary table on the volume on which the specified partition resides.

If a query involves a hash join or a hash grouping, SQL creates temporary files on
the program swap volume.

If a query requires a sort operation but does not use FastSort, SQL creates any
temporary tables used for the sort on the volume on which the table being sorted
resides. If the table is partitioned, SQL creates any temporary table on the same
partition as the partition being sorted.

If SQL uses FastSort to sort a table, FastSort’s SORTPROG process creates a
temporary file on the SORTPROG scratch volume.
To redirect temporary tables created by SQL, use the =_SQL_TM_node_vol DEFINE.
To redirect temporary tables created by SORTPROG, use the =_SORT_DEFAULTS
DEFINE.
If SMF is installed on your node, temporary table placement for serial plans is
unchanged. For example, if the outermost table in a join resides on a virtual volume,
SQL creates a temporary table on the same virtual volume. However, for parallel plans
SQL places temporary tables only on physical volumes. Virtual volumes are never
candidates for temporary tables SQL creates during a parallel operation.
TIME_FORMAT Option
TIME_FORMAT is an option of the SQLCI report writer SET STYLE command that
defines a default print format for items specified with AS TIME *.
time-format
is a format to use as the default format for print items specified with AS TIME *. For
information about how to specify formats, see AUDIT File Attribute on page A-74.
The default format is HP2:M2:S2.
Example—TIME_FORMAT
This example sets a new default time format:
>> SET STYLE TIME_FORMAT "HB:M2:S2:C2:T3";
TIME_FORMAT “time-format”