Temporary table information
A temporary table is used as a work table during access plan execution. Generally, temporary tables are used when subqueries need to be evaluated early in the access plan, or when intermediate results will not fit into the available memory.
If a temporary table is needed, one of the following statements will appear in db2expln command output.
The ID is an identifier that is assigned by db2expln for convenience when referring to the temporary table. This ID is prefixed with the letter 't' to indicate that the table is a temporary table.
Insert Into Temp Table ID = tn --> ordinary temporary table Insert Into Shared Temp Table ID = tn --> ordinary temporary table will be created by multiple subagents in parallel Insert Into Sorted Temp Table ID = tn --> sorted temporary table Insert Into Sorted Shared Temp Table ID = tn --> sorted temporary table will be created by multiple subagents in parallel Insert Into Global Temp Table ID = ts,tn --> declared global temporary table Insert Into Shared Global Temp Table ID = ts,tn --> declared global temporary table will be created by multiple subagents in parallel Insert Into Sorted Global Temp Table ID = ts,tn --> sorted declared global temporary table Insert Into Sorted Shared Global Temp Table ID = ts,tn --> sorted declared global temporary table will be created by multiple subagents in parallel
Each of these statements is followed by:
which indicates how many columns there are in each row that is being inserted into the temporary table.
#Columns = n
Sorted temporary tables
Sorted temporary tables can result from such operations as:
- ORDER BY
- GROUP BY
- Merge join
- '= ANY' subquery
- '<> ALL' subquery
- INTERSECT or EXCEPT
- UNION (without the ALL keyword)
A number of statements that are associated with a sorted temporary table can appear in db2expln command output.
- The following statement indicates the number of key columns that
are used in the sort:
One of the following lines is displayed for each column in the sort key:
#Sort Key Columns = n
Key n: column_name (Ascending) Key n: column_name (Descending) Key n: (Ascending) Key n: (Descending)
- The following statements provide estimates of the number of rows
and the row size so that the optimal sort heap can be allocated at
Sortheap Allocation Parameters: | #Rows = n | Row Width = n
- The following statement is displayed if only the first rows of
the sorted result are needed:
Sort Limited To Estimated Row Count
- For sorts that are performed in a symmetric multiprocessor (SMP)
environment, the type of sort that is to be performed is indicated
by one of the following statements:
Use Partitioned Sort Use Shared Sort Use Replicated Sort Use Round-Robin Sort
- The following statements indicate whether or not the sorted result
will be left in the sort heap:
If a piped sort is indicated, the database manager will keep the sorted output in memory, rather than placing it in another temporary table.
Piped Not Piped
- The following statement indicates that duplicate values will be
removed during the sort operation:
- If aggregation is being performed during the sort operation, one
of the following statements is displayed:
Partial Aggregation Intermediate Aggregation Buffered Partial Aggregation Buffered Intermediate Aggregation
Temporary table completion
A completion statement is displayed whenever a temporary table is created within the scope of a table access. This statement can be one of the following:
Temp Table Completion ID = tn Shared Temp Table Completion ID = tn Sorted Temp Table Completion ID = tn Sorted Shared Temp Table Completion ID = tn
Table functions are user-defined functions (UDFs) that return data to the statement in the form of a table. A table function is indicated by the following statements, which detail the attributes of the function. The specific name uniquely identifies the table function that is invoked.
Access User Defined Table Function | Name = schema.funcname | Specific Name = specificname | SQL Access Level = accesslevel | Language = lang | Parameter Style = parmstyle | Fenced Not Deterministic | Called on NULL Input Disallow Parallel | Not Federated Not Threadsafe