Teradata 简明教程

Teradata - Statistics

Teradata 优化器会针对每个 SQL 查询提出执行策略。此执行策略基于在 SQL 查询所用表中收集的统计信息。使用 COLLECT STATISTICS 命令收集表的统计信息。优化器需要环境信息和数据人口统计信息来提出最佳执行策略。

Teradata optimizer comes up with an execution strategy for every SQL query. This execution strategy is based on the statistics collected on the tables used within the SQL query. Statistics on the table is collected using COLLECT STATISTICS command. Optimizer requires environment information and data demographics to come up with optimal execution strategy.

Environment Information

  1. Number of Nodes, AMPs and CPUs

  2. Amount of memory

Data Demographics

  1. Number of rows

  2. Row size

  3. Range of values in the table

  4. Number of rows per value

  5. Number of Nulls

有三种方法可用于收集表中的统计信息。

There are three approaches to collect statistics on the table.

  1. Random AMP Sampling

  2. Full statistics collection

  3. Using SAMPLE option

Collecting Statistics

COLLECT STATISTICS 命令用于收集表中的统计信息。

COLLECT STATISTICS command is used to collect statistics on a table.

Syntax

以下是收集表中的统计信息的语法。

Following is the basic syntax to collect statistics on a table.

COLLECT [SUMMARY] STATISTICS
INDEX (indexname) COLUMN (columnname)
ON <tablename>;

Example

以下示例收集 Employee 表的 EmployeeNo 列中的统计信息。

The following example collects statistics on EmployeeNo column of Employee table.

COLLECT STATISTICS COLUMN(EmployeeNo) ON Employee;

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

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

*** Update completed. 2 rows changed.
*** Total elapsed time was 1 second.

Viewing Statistics

您可以使用 HELP STATISTICS 命令查看收集到的统计信息。

You can view the collected statistics using HELP STATISTICS command.

Syntax

以下是查看所收集统计信息的语法。

Following is the syntax to view the statistics collected.

HELP STATISTICS <tablename>;

Example

以下是查看 Employee 表中收集到的统计信息的示例。

Following is an example to view the statistics collected on Employee table.

HELP STATISTICS employee;

当执行上面的查询时，会生成以下结果。

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

  Date       Time      Unique Values           Column Names
--------   -------- -------------------- -----------------------
16/01/01   08:07:04         5                       *
16/01/01   07:24:16         3                   DepartmentNo
16/01/01   08:07:04         5                   EmployeeNo