The following formula is used to calculate the size of the work table file that is to be used by one SQL statement.

Formula

Size of work table file to be used by one SQL statement (bytes) = a b + c d

a: Size of a column information work table

b: Maximum number of column information work tables

c: Size of a location information work table

d: Maximum number of location information work tables

(a) Obtaining the size of a column information work table

To obtain the size of a column information work table, use the following formula:

Formula

Size of a column information work table (bytes) = a MIN{ (b - 48) c , 255 } b 2

a: Number of rows in the column information work table (see Table 19-2)

b: Page length of the work table (use Formula 1 as follows)

c: Row length of the work table (use Formula 2 as follows)

Formula 1

Page length for a work table# = MAX { (row length for work table + 48) 2048 2048, 4096}

#: The page length of a work table must be no greater than 32,768 bytes.

Formula 2

Row length for a work table* = n Ai + 2n + 6 i=1

A_i:

Data length for each column in work table (see Table 19-3 for the calculation procedure)

n:

Number of columns in work table (see Table 19-3 for the calculation procedure)

^* The row length of a work table must be no greater than 32,720 bytes.

If the LIMIT clause is specified and the value of (number of offset rows + number of rows specified in the LIMIT clause) is 32,768 or greater, add 12 to the row length of the work table obtained from Formula 2. However, addition of 12 is not necessary in the following cases:

• The table to be searched is locked in the EX mode.
• WITHOUT LOCK was specified.
• The rapid grouping facility was specified.
• Multiple tables are to be joined.
  

  Table 19-2 Obtaining the number of rows in a column information work table

  SQL statement	Number of rows in column information work table
  SELECT statement	This is the total number of rows subject to retrieval in individual tables. If multiple tables are joined, then use the resulting number of rows, if it is greater.
  CREATE INDEX statement	This is the number of rows in a table. For an index for repetition columns, use the total number of elements per repetition column among the index component columns.

  Table 19-3 Obtaining the data length for each column and the number of columns in a work table

  SQL statement	n	Ai
  SELECT statement	Number of columns specified in selection expression + number of columns specified in GROUP BY clause + number of columns specified in ORDER BY clause + number of columns specified in HAVING clause + 1 if the FOR UPDATE clause is specified If ROW is specified in the selection expression, specify the total number of rows in the table.	Data length for each column However, in the case of large object data (BLOB), character data with a defined length of 256 or greater (including National and mixed character data), or binary data for columns that do not have the following attributes or for location information columns: 12 Column specified in a join condition(join column) Selection expression with DISTINCT clause specified Column specified in the subquery selection expression with a quantified predicate Column specified in the subquery selection expression with the IN predicate Selection expression in a query specification subject to set operation with UNION[ALL] or EXCEPT[ALL] Column specified in the ORDER BY clause
  CREATE INDEX statement	1 (index information column) + 1(positional information column)	For an index information column, specify the sum of the data lengths for index component columns 12 for a positional information column

Note

For details about the data lengths of columns, see the following tables:

• Table 17-1 List of data lengths
• Table 17-2 Data lengths for the variable-length character string type (except abstract data type and repetition columns)
• Table 17-3 Data lengths for the variable-length character string type (abstract data type)
• Table 17-4 Data lengths for the variable-length character string type (repetition columns)

(b) Obtaining the maximum number of column information work tables

Table 19-4 shows the procedure for obtaining the maximum number of column information work tables.

Table 19-4 Obtaining the maximum number of column information work tables

SQL statement	Maximum number of work tables for storing column information
SELECT statement	When none of 1-10 as follows is applicable: 0 When any of 1-10 as follows is applicable: Sum of all the applicable values from 1-10 When multiple tables are joined for retrieval Number of additional work tables (HiRDB/Single Server) = number of joined tables + 1 Number of additional work tables (HiRDB/Parallel Server) = number of joined tables 2 If the join key column has an index and there is a limitation condition, the number of work tables is 0. When specifying the ORDER BY clause Number of additional work tables = 2 For a non-partitioned table, if the column with the ORDER BY clause specified has an index, the number of work tables is 0. When specifying a value expression containing a set function in the selection expression without specifying the GROUP BY clause* Number of additional work tables = 1 When specifying the GROUP BY clause Number of additional work tables = number of GROUP BY clauses specified 2 When specifying the DISTINCT clause Number of additional work tables = number of DISTINCT clauses specified 2 When specifying the UNION[ALL] or EXCEPT[ALL] clause Number of additional work tables (HiRDB/Single Server) = number of UNION[ALL] or EXCEPT[ALL] clauses specified + 2 Number of additional work tables (HiRDB/Parallel Server) = (number of UNION[ALL] or EXCEPT[ALL] clauses specified + 1) 2 When specifying the FOR UPDATE clause or when using this cursor for updating purposes and specifying a search condition for a column with the index defined* Number of additional work tables = 2 When specifying the FOR READ ONLY clause Number of additional work tables = 1 When specifying a subquery (quantified predicate) Number of additional work tables = number of subqueries specified + (number of =ANY quantified predicates for a column with the index defined) + (number of IN predicate subqueries specified for a column with the index defined) + (number of =SOME quantified predicates for a column with the index defined) When specifying the window function COUNT(*) OVER() in a selection expression Number of increased work tables = number of query specifications in which the window function is specified in the selection expression
CREATE INDEX statement	2

^* Applicable only to a HiRDB/Parallel Server.

(c) Obtaining the size of a location information work table

To obtain the size of a location information work table, use the following formula:

Formula

Size of a location information work table (bytes) = a 184* 4096 2

^* If an index-type plug-in function is specified as the search condition, use the value 155.

a: Number of rows in the location information work table

The following shows the procedure for obtaining the number of rows in the location information work table:

SQL statement

Obtaining the number of rows in the location information work table

SELECT statement UPDATE statement DELETE statement

If the search condition contains one predicate that includes a column with the index defined, use the number of rows for which the predicate is true. If there is more than one predicate, use the sum of the following values: If OR operation is conducted on the predicates, the total number of rows for which at least one predicate is true. If AND operation is conducted on the predicates, sum of the larger numbers of rows for which the predicates are true.

(d) Obtaining the maximum number of location information work tables

Table 19-5 shows the procedure for obtaining the maximum number of location information work tables.

Table 19-5 Obtaining the maximum number of location information work tables

SQL statement	Maximum number of location information work tables
SELECT statement	Number of indexes to be used during search + 1 in either of the following cases: Search condition is specified for multiple columns with index defined. The FOR UPDATE clause is specified or this cursor is used for updating purposes and a search condition is specified for the column with index defined.* Search condition is specified for a column for which a repetition column index is defined. Facility for batch acquisition from functions provided by plug-ins is specified as the SQL optimization option and a function provided by a plug-in that uses a plug-in index is specified as a search condition. In all these cases, the value is the number of indexes used during a search + 1.
UPDATE statement DELETE statement	If the search condition contains a column with an index defined, use the number of indexes used during search processing + 1.

^* This is applicable to a HiRDB/Single Server only.

To obtain the size of a work table file used by the ASSIGN LIST statement, use the following formula:

Formula

Size of work table file to be used by the ASSIGN LIST statement (bytes) = n (Bi 2) i = 1

n: Number of predicates in the selection condition of the ASSIGN LIST statement

B_i: Size of the work table used to process predicate i in the search condition. Use the following formula to obtain this value:

Bi =  number of rows for which predicate i is true in the base table for the list*  504   4096  1.5 (bytes)

^* If the predicate is a condition for a repetition column, this value is the total number of elements that are true.