Nonstop Database, HiRDB Version 9 Description
A temporary table is a base table that exists only during a transaction or SQL session. A temporary table that exists only during a transaction is called a transaction-specific temporary table, and a temporary table that exists only during an SQL session is called an SQL session-specific temporary table.
No temporary table is created when a table is defined. A table is created when the INSERT statement is executed on a temporary table for the first time. This process is called the instantiation of a temporary table.
Furthermore, an exclusive temporary table is created each time HiRDB connects to a table (by executing the CONNECT statement). Therefore, even if multiple users use temporary tables concurrently, the tables are not affected by the data operations performed by users (referencing, inserting, updating, or deleting). Temporary tables and the indexes defined in them (temporary table indexes) are stored in the temporary table RDAREA, and are automatically deleted when a transaction is completed or when an SQL session ends.
The following figure provides an overview of temporary tables.
Figure 3-23 Overview of temporary tables
For details about temporary tables, see the HiRDB Version 9 Installation and Design Guide.
The effective data duration of an instantiated temporary table (the duration during which the actual body exists) differs depending on whether the temporary table is a transaction-specific temporary table, or an SQL session-specific temporary table. The following table shows start and end times for the effective data duration of temporary tables.
Table 3-6 Start and end timing for the effective data duration of temporary tables
Temporary table type | Start timing | End timing |
---|---|---|
Transaction-specific temporary table | When the INSERT statement is executed on a temporary table during the transaction for the first time | When the transaction is completed |
SQL session-specific temporary table | When the INSERT statement is executed on a temporary table during the SQL session for the first time |
|
In the CREATE TABLE definition SQL, specify GLOBAL TEMPORARY. To define a transaction-specific temporary table, specify ON COMMIT DELETE ROWS; to define an SQL session-specific temporary table, specify ON COMMIT PRESERVE ROWS. Note that some operands cannot be specified or are ignored even if specified for temporary tables. For details, see CREATE TABLE in the manual HiRDB Version 9 SQL Reference.
Temporary table indexes are defined in essentially the same way as ordinary indexes. As is the case with temporary tables, some operands cannot be specified or are ignored even if specified for temporary table indexes. For details, see the CREATE INDEX sections in the manual HiRDB Version 9 SQL Reference.
Specify the name of the temporary table RDAREA to be used in the PDTMPTBLRDAREA client environment definition. If multiple RDAREAs are specified or if this environment definition is omitted, HiRDB determines the temporary table RDAREA for storing data according to the following rules:
Note that specification of PDTMPTBLRDAREA is ignored on an XDS client, and therefore specification is considered omitted.
For details about the rules for determining the storage destination RDAREA, see the HiRDB Version 9 Installation and Design Guide.
The operation commands and utilities listed below cannot be executed on temporary tables. For details, see the manual HiRDB Version 9 Command Reference.
The SQL statements listed below cannot specify temporary tables or temporary table indexes. For details, see the manual HiRDB Version 9 SQL Reference.
All Rights Reserved. Copyright (C) 2015, Hitachi, Ltd.