(a) SQL statements that require a work table file

A work table file is used when multiple tables are joined by a SELECT statement for retrieval or during execution of certain SQL processing, such as CREATE INDEX.

The following types of SQL processing require work table files:

1. Retrieval specifying the UNION [ALL] or EXCEPT [ALL] clause
2. Specification in an UPDATE or DELETE statement of a retrieval condition based on a column for which an index is defined
3. DROP SCHEMA
4. DROP TABLE
5. DROP INDEX
6. Revocation with REVOKE statement of access privileges
7. CREATE INDEX
8. Creation with ASSIGN LIST statement of a list from a base table
9. Specification in a SELECT statement of any of the following:
   
   • Retrieval by joining multiple tables

• Specification of ORDER BY clause for a column for which an index is not defined
• Specification of ORDER BY clause for a row-partitioned table
• Specification of a value expression containing a set function in a selection expression^*
• Specification of a value expression containing the COUNT(*) OVER() window function in a selection expression
• Specification of GROUP BY clause
• Specification of DISTINCT clause
• Specification of a retrieval condition based on multiple columns for which an index is defined
• Specification of a retrieval condition for a column for which a repetition column index is defined
• Specification of the facility for batch acquisition from functions provided by plug-ins for the SQL optimization option, and specification of and searching for functions provided by plug-ins that use a plug-index for a retrieval condition.
• Specification of a retrieval condition based on a column for which an index is defined, and either a FOR UPDATE clause is specified or there is an update using this cursor
• Specification of a FOR READ ONLY clause
• Specification of a subquery of a quantified predicate
• Specification of a subquery of the IN predicate
• Creation of an internally derived table in a retrieval from a view table or a retrieval in which a WITH clause is specified

^* Applies to a HiRDB/Parallel Server only; not applicable to a HiRDB/Single Server.

(b) Operations that require a work table file

The following operations require a work table file:

• Batch creation of an index
• Re-creation of an index
• Reorganization of an index
• Execution of the rebalancing utility

Work table files are required at the following servers:

• Single servers
• Dictionary servers
• Back-end servers

The pdfmkfs command is used to create a HiRDB file system area for work table files. Additionally, you specify the pdwork operand of the server definition to create an environment in which a HiRDB file system area for work table files can be created. The name of the HiRDB file system area for work tables is specified in the pdwork operand.

For details (size, etc.) about designing and creating work table files, see the HiRDB Version 8 Installation and Design Guide.

Reference note

If you use either of the following environment setup support tools when you install HiRDB for the first time, a HiRDB file system area for work table files will be created based on the information you enter (the pdwork operand will also be configured):

• Simple setup tool
• Batch file (SPsetup.bat)