Teradata 简明教程

Teradata - Secondary Index

表只能包含一个主键索引。更常见的是，您将遇到表包含其他列的情况，其中使用经常访问数据。Teradata 将对这些查询执行全表扫描。辅助索引解决了此问题。

A table can contain only one primary index. More often, you will come across scenarios where the table contains other columns, using which the data is frequently accessed. Teradata will perform full table scan for those queries. Secondary indexes resolve this issue.

辅助索引是访问数据的替代路径。主键索引与辅助索引之间存在一些差异。

Secondary indexes are an alternate path to access the data. There are some differences between the primary index and the secondary index.

  1. Secondary index is not involved in data distribution.

  2. Secondary index values are stored in sub tables. These tables are built in all AMPs.

  3. Secondary indexes are optional.

  4. They can be created during table creation or after a table is created.

  5. They occupy additional space since they build sub-table and they also require maintenance since the sub-tables need to be updated for each new row.

有两种类型的二级索引 -

There are two types of secondary indexes −

  1. Unique Secondary Index (USI)

  2. Non-Unique Secondary Index (NUSI)

Unique Secondary Index (USI)

唯一二级索引仅允许定义为 USI 的列的唯一值。通过 USI 访问行是两 amp 操作。

A Unique Secondary Index allows only unique values for the columns defined as USI. Accessing the row by USI is a two amp operation.

Create Unique Secondary Index

以下示例在 employee 表的 EmployeeNo 列上创建 USI。

The following example creates USI on EmployeeNo column of employee table.

CREATE UNIQUE INDEX(EmployeeNo) on employee;

Non Unique Secondary Index (NUSI)

非唯一二级索引允许定义为 NUSI 的列的重复值。通过 NUSI 访问行是全部-amp 操作。

A Non-Unique Secondary Index allows duplicate values for the columns defined as NUSI. Accessing the row by NUSI is all-amp operation.

Create Non Unique Secondary Index

以下示例在 employee 表的 FirstName 上创建 NUSI。

The following example creates NUSI on FirstName column of employee table.

CREATE INDEX(FirstName) on Employee;