Index on DB2 for z/OS: DB2 for z/OS 的索引

可以创建在任何表上的索引：

Unique Index:
An index that ensures that the value in a particular column or set of columns is unique.

Primary index:
A unique index on the primary key of the table.

Secondary index:
An index that is not a primary index.

Clustering index:
An index that ensures a logical grouping. When data is inserted into the table, the clustering index attempts to maintain the clustering sequence within the partition.

Expression-based index:
An index that is based on a general expression. Use expression-based indexes when you want an efficient evaluation of queries that involve a column-expression.In the CREATE INDEX or ALTER INDEX statement, the index key is defined as an expression rather than a column or set of columns.     

 

接下来这些索引只能创建在Range partitioned表上：

Partitioned index：
An index that is physically partitioned.A partitioned index consists of multiple data sets. Each data set corresponds to a table partition.

Partitioning index (PI)：
An index that corresponds to the columns that partition the table. These columns are called the partitioning key and are specified in the PARTITION BY clause of the CREATE TABLE statement.All partitioning indexes must also be partitioned. Partitioning indexes are not required.

Secondary index：
An index that is not a partitioning index in partitioned tables.

Data partitioned secondary index (DPSI):
A partitioned index that is not a partitioning index.These indexes are also called partitioned secondary indexes (PSIs).

Nonpartitioned secondary index (NPSI):
An index that is not partitioned or partitioning.These indexes are also called nonpartitioned indexes (NPIs).

Multi-piece index：
A nonpartitioned index that has multiple data sets. The data sets do not correspond to data partitions.Use a multi-piece index to spread a large index across multiple data sets and thus reduce the physical I/O contention on the index.

 

只能创建在含有XML列的表上的索引：

XML index：
An index that uses a particular XML pattern expression to index paths and values in XML documents that are stored in a single XML column.

 

所有索引都能有的共同属性：

Padded：
Any varying-length string columns in the index are padded with the default pad character to their maximum length.

Compressed：
The data is compressed to reduce the size of the index on disk.