5.10.2 Work tables created when SQL statements are executed

Work tables are created when you execute the SQL statements listed in the table below.

Important: If the row length of the work table exceeds the maximum row length, the SQL statement will result in an error. For details about the maximum row length of a work table, see Maximum and minimum values related to database in the HADB Setup and Operation Guide. For details about how to determine the row length of a work table, see the description of variable ROWSZ in Determining the number of pages for base rows that are needed for storing work tables in the HADB Setup and Operation Guide.

Table 5‒11: SQL statements for which work tables are created
No.	SQL statement for which work table is created		Purpose of work table	Columns of work table	Work table type
1	`ORDER` `BY` clause is specified.	Results of a set operation are used as a sort key.	Sorting the retrieval results	Store the values of columns resulting from query expressions	Local work table
1	`ORDER` `BY` clause is specified.	Results of a query specification are used as a sort key.	Sorting the retrieval results	Store the results of value expressions specified for sort keys in the `ORDER` `BY` clause Store the row IDs of the rows in the retrieval results for the base table (when the base table is specified in the `FROM` clause)^#1 Store the values of retrieval target columns (when the base table with key scan used as the table retrieval method is specified in the `FROM` clause) Store the values of retrieval target columns in the column store table^#11 (when a column store table is specified in the `FROM` clause) Store the column values of results derived from query expression bodies in derived tables (when a derived table is specified in the `FROM` clause)^#6 Store the column values of results derived from query expressions in view definitions (when a viewed table is specified in the `FROM` clause)^#6 Store the column values of results derived from query expression bodies in `WITH` clauses (when a query name is specified in the `FROM` clause)^#6 Store the column values of results derived from system-defined functions specified in table function derived tables (when a table function derived table is specified in the `FROM` clause) Store the values resulting from the scalar function `RANDOMROW` (when the scalar function `RANDOMROW` is specified in the selection expression)	Local work table
2	`GROUP` `BY` clause is specified.	The grouping method is global hash grouping.^#2	Retaining the grouping results. This work table is used when a shortage occurs in the hash table area. The size of the hash table area is specified in the `adb_sql_exe_hashtbl_area_size` operand in the server definition or the client definition.	Store the values resulting from the value expressions specified for grouped columns in the `GROUP` `BY` clause Store the results of set functions Store information the HADB server uses for hashing^{#9, #10}	Local work table^#7 Global work table^#8
		The grouping method is local hash grouping^#2	Sorting for grouping. This work table is used when there is insufficient hash grouping area. The size of the hash grouping area is specified in the `adb_sql_exe_hashgrp_area_size` operand in the server or client definition.	Store the values resulting from the value expressions specified for grouped columns in the `GROUP` `BY` clause Store the columns specified in the arguments of set functions Store the results of set functions	Local work table
		The grouping method is sort grouping^#2.	Sorting for grouping.		Local work table
3	`SELECT` `DISTINCT` is specified.	The method for processing `SELECT DISTINCT` is hash execution.^#12	Retaining retrieval results. This work table is used when a shortage occurs in the hash table area. The size of the hash table area is specified in the `adb_sql_exe_hashtbl_area_size` operand in the server definition or the client definition.	Store the values of columns resulting from selection expressions Store information HADB uses for hashing^{#9, #10}	Local work table^#7 Global work table^#8
3	`SELECT` `DISTINCT` is specified.	The method for processing `SELECT DISTINCT` is work table execution.^#12	Sorting retrieval results or eliminating duplicate retrieval results	Store the values of columns resulting from selection expressions	Local work table
4	`DISTINCT` set function or inverse distribution function is specified.	The grouping method is global hash grouping.^#2	Retaining the input values of set functions whose duplicates have been eliminated. This work table is used when a shortage occurs in the hash table area. The size of the hash table area is specified in the `adb_sql_exe_hashtbl_area_size` operand in the server definition or the client definition.	Store the values resulting from the value expressions specified for grouped columns in the `GROUP` `BY` clause Store the values of columns specified in the arguments of the `ALL` set function Store the results of value expressions specified in the arguments of the `DISTINCT` set function Store the results of value expressions specified for a sort key in the `WITHIN` group specification in an inverse distribution function Store the results of value expressions specified in the arguments of the `MEDIAN` inverse distribution function Store information the HADB server uses for hashing^{#9, #10}	Local work table^#7 Global work table^#8
4		The grouping method is not global hash grouping.^#2	Eliminating the duplicates of input values for set functions or sorting the input values for set functions		Local work table^#7 Global work table^#8
5	Window functions are specified.		Sorting for obtaining the results of window functions	Store the values of columns of a table specified in the `FROM` clause Store the values resulting from the window function specified in the selection expression	Local work table
6	Multiple table references are specified in the `FROM` clause.	The table joining method is hash join.^#3	Retaining the results of table references subject to table join processing. This work table is used when a shortage occurs in the hash table area. The size of the hash table area is specified in the `adb_sql_exe_hashtbl_area_size` operand in the server definition or the client definition.	Store the values of columns of a table specified in the `FROM` clause Store information the HADB server uses for hashing^{#9, #10}	Local work table^#7 Global work table^#8
6		The table joining method is nested loop join.^#3	Retaining the results of table references subject to table join processing		Global work table
7	Derived tables are specified.		Retaining the results of query expression bodies corresponding to derived tables	Store the values of columns resulting from a query expression body in derived tables^#6	Global work table
8	Viewed tables are specified.		Retaining the results of query expressions corresponding to viewed tables	Store the values of result columns derived from a query expression in view definitions^#6	Global work table
9	`WITH` clause is specified.		Retaining the results of query expression bodies corresponding to query names	Store the values of columns resulting from a query expression body in the `WITH` clause^#6	Global work table
10	A table function derived table is specified.		Retaining the results of system-defined functions that derive table function derived tables.	Store the values resulting from the system-defined function	Global work table
11	Joined tables are specified.		Retaining the results of joined tables. For details about the work tables used to obtain the results of joined tables, see Multiple table references are specified in the FROM clause in row 6.	Store the values of columns in joined tables	Global work table
12	Subqueries are specified.	The subquery processing method is hash execution.^#4	Retaining the results of subqueries. This work table is used when a shortage occurs in the hash table area. The size of the hash table area is specified in the `adb_sql_exe_hashtbl_area_size` operand in the server definition or the client definition.	Store the values of columns resulting from a selection expression in subqueries Store the values of external reference columns contained in subqueries^#5 Store the results of set functions contained in subqueries Store information the HADB server uses for hashing^{#9, #10}	Local work table^#7 Global work table^#8
12	Subqueries are specified.	The subquery processing method is not hash execution.^#4	Retaining the results of subqueries	Store the values of columns resulting from a selection expression in subqueries	Global work table
13	A set operation is specified.	The method for processing the set operation is hash execution.^#13	Retaining the results of deduplication. This work table is used when a shortage occurs in the hash table area. The size of the hash table area is specified in the `adb_sql_exe_hashtbl_area_size` operand in the server definition or the client definition.	Store the values of columns resulting from the selection expression specified in a query specification Store information HADB uses for hashing^{#9, #10}	Local work table^#7 Global work table^#8
13	A set operation is specified.	The method for processing the set operation is work table execution.^#13	Sorting retrieval results or eliminating duplicate retrieval results	Store the values of columns resulting from the selection expression specified in a query specification	Local work table
14	A recursive query is specified.		Retaining the results of anchor members and recursive members	Store the values of columns resulting from a recursive query	Global work table

#1

The row ID is a value that indicates a row's storage location. The data type of a row ID is CHAR(16).

#2

For details about grouping methods, see 5.7 Grouping methods.

#3

For details about table joining methods, see 5.5 Table joining methods.

#4

For details about how to process subqueries, see 5.6 How to process subqueries.

#5

For details about external reference columns, see Specification format and rules for subqueries in the manual HADB SQL Reference.

#6

Expansion of the internal derived table might result in this table being handled as the base table. For details about expansion of internal derived tables, see Internal derived tables in the manual HADB SQL Reference.

#7

A work table used to store data that the hash table area cannot accommodate due to insufficient space.

#8

A work table used to store data that cannot be processed because there is still insufficient hash table area after data has been stored in work tables.

#9

The data type of columns that store the information HADB uses for hashing is INTEGER.

#10

A column created in a work table used to store data that cannot be processed because there is still insufficient hash table area after data has been stored in work tables.

#11

Columns with a definition length of 128 or more bytes are not subject to retrieval. A column with a definition length of 128 or more bytes refers to any of the following columns:

A column of CHARACTER type with a definition length of 128 or more bytes
A column of VARCHAR type with a definition length of 128 or more bytes
A column of BINARY type with a definition length of 128 or more bytes
A column of VARBINARY type with a definition length of 128 or more bytes

#12

For details about the method for processing SELECT DISTINCT, see 5.9 Method for processing SELECT DISTINCT.

#13: For details about the method for processing the set operation, see 5.8 Methods for processing set operations.

You can find out whether work tables were created by checking the access path after the SQL statement has executed. For details about access paths, see the following sections:

How to check access paths

See 6.1.2 How to check access paths.
Information displayed in access paths

See (7) Work table creation information in 6.1.4 Information displayed in the tree view.

To Page Top