Db2 简明教程

DB2 - Indexes

本章涵盖索引简介、索引类型、索引创建和丢弃。

This chapter covers introduction to indexes, their types, creation and dropping.

Introduction

索引是一组指针，可以引用表中的行、MDC 或 ITC 表中的块、XML 存储对象中由一个或多个键的值逻辑排序的 XML 数据。在 DB2 表列上创建索引以加快查询的数据访问速度，高效地对数据进行聚集和分区。它还可以提高对视图的操作性能。具有唯一索引的表可以包含具有唯一键的行。根据表要求，你可以采用不同类型的索引。

Index is a set of pointers, which can refer to rows in a table, blocks in MDC or ITC tables, XML data in an XML storage object that are logically ordered by the values of one or more keys. It is created on DB2 table columns to speed up the data access for the queries, and to cluster and partition the data efficiently. It can also improve the performance of operation on the view. A table with a unique index can have rows with unique keys. Depending on the table requirements, you can take different types of indexes.

Types of indexes

  1. Unique and Non-Unique indexes

  2. Clustered and non-clustered indexes

Creating indexes

若要创建唯一索引，请使用以下语法：

For creating unique indexes, you use following syntax:

Syntax:

Syntax:

db2 create unique index <index_name> on
<table_name>(<unique_column>) include (<column_names..>)

Example ：为“shopper.sales1”表创建索引。

Example: To create index for “shopper.sales1” table.

db2 create unique index sales1_indx on
shopper.sales1(id) include (itemname)

Dropping indexes

若要丢弃索引，请使用以下语法：

For dropping the index, you use the following syntax:

Syntax:

Syntax:

db2 drop unique index <index_name> on
<table_name>(<unique_column>) include (<column_names..>)

Example:

Example:

db2 drop index sales_index