Working with Global Temporary Tables

When building temporary tables on an Oracle database, you have the option of implementing Global Temporary Tables (GTT) for enhanced performance.

This example shows the Record Type tab when creating a temporary table on the Oracle platform with the Global Temporary Table option displayed.

Example of the Global Temporary Table (GTT) option on the Record Type tab

When building a GTT, using the Application Designer Build feature, the SQL generated to build the table appears similar to the following example:

CREATE GLOBAL TEMPORARY TABLE PS_QE_AETEST (PROCESS_INSTANCE
 DECIMAL(10) NOT null,
   QE_AE_INT_1 SMALLINT NOT null,
   QE_AE_APPLID VARCHAR2(12) NOT null,
   QE_AE_SECTION VARCHAR2(8) NOT null,
   QE_AE_STEP VARCHAR2(8) NOT null,
   JOB_INSTANCE DECIMAL(10) NOT null,
   PROCESSINSTANCE INTEGER NOT null,
   QE_RETURN_CD SMALLINT NOT null,
   RUN_CNTL_ID VARCHAR2(30) NOT null,
   AS_OF_DATE DATE NOT null,
   QE_AE_SQLROWS INTEGER NOT null,
   CURRENCY_CD VARCHAR2(3) NOT null,
   ANNUAL_RT DECIMAL(18, 3) NOT null) ON COMMIT PRESERVE ROWS
 TABLESPACE PTAPP
/
CREATE UNIQUE iNDEX PS_QE_AETEST ON PS_QE_AETEST (PROCESS_INSTANCE)
/

Note:

If you select the Global Temporary Table option, the temporary table name cannot exceed 11 characters.

Note:

A Global Temporary Table must be assigned to the PSGTTnn tablespace, where nn is the tablespace number (01, 02, and so on).

Global Temporary Tables are discussed in more detail in the product documentation for:PeopleTools: Data Management. See Data Management: Improving Process Performance with Global Temporary Tables. They are also discussed in the product documentation for PeopleTools: Application Engine. See Application Engine: Understanding Global Temporary Tables.