Teradata 简明教程

Teradata - Table Types

Teradata 支持以下表格类型来保存临时数据。

Teradata supports the following table types to hold temporary data.

  1. Derived Table

  2. Volatile Table

  3. Global Temporary Table

Derived Table

导出表在查询中创建、使用和删除。它们用于在查询中存储中间结果。

Derived tables are created, used and dropped within a query. These are used to store intermediate results within a query.

Example

以下示例使用工资超过 75000 的员工记录构建了导出表 EmpSal。

The following example builds a derived table EmpSal with records of employees with salary greater than 75000.

SELECT
Emp.EmployeeNo,
Emp.FirstName,
Empsal.NetPay
FROM
Employee Emp,
(select EmployeeNo , NetPay
from Salary
where NetPay >= 75000) Empsal
where Emp.EmployeeNo = Empsal.EmployeeNo;

当执行以上查询时，它将返回工资超过 75000 的员工。

When the above query is executed, it returns the employees with salary greater than 75000.

*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
 EmployeeNo            FirstName               NetPay
-----------  ------------------------------  -----------
    103                  Peter                 83000

Volatile Table

易失表在用户会话中创建、使用和删除。它们的定义未存储在数据字典中。它们保存查询中经常使用的中间数据。以下是语法。

Volatile tables are created, used and dropped within a user session. Their definition is not stored in data dictionary. They hold intermediate data of the query which is frequently used. Following is the syntax.

Syntax

CREATE [SET|MULTISET] VOALTILE TABLE tablename
<table definitions>
<column definitions>
<index definitions>
ON COMMIT [DELETE|PRESERVE] ROWS

Example

CREATE VOLATILE TABLE dept_stat (
   dept_no INTEGER,
   avg_salary INTEGER,
   max_salary INTEGER,
   min_salary INTEGER
)
PRIMARY INDEX(dept_no)
ON COMMIT PRESERVE ROWS;

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

*** Table has been created.
*** Total elapsed time was 1 second.

Global Temporary Table

全局临时表的定义存储在数据字典中，并且可以在许多用户/会话中使用它们。但加载到全局临时表中的数据仅在会话期间保留。每个会话最多可以实现 2000 个全局临时表。以下是语法。

The definition of Global Temporary table is stored in data dictionary and they can be used by many users/sessions. But the data loaded into global temporary table is retained only during the session. You can materialize up to 2000 global temporary tables per session. Following is the syntax.

Syntax

CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename
<table definitions>
<column definitions>
<index definitions>

Example

CREATE SET GLOBAL TEMPORARY TABLE dept_stat (
   dept_no INTEGER,
   avg_salary INTEGER,
   max_salary INTEGER,
   min_salary INTEGER
)
PRIMARY INDEX(dept_no);

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

*** Table has been created.
*** Total elapsed time was 1 second.