创建IBM i上的32K索引

Body

原文链接：

早在远古时期，索引已经被广泛用于组织和检索数据。史前时代的穴居人用原始的索引管理长毛象（猛犸象）和剑齿虎。这些索引非常小 — 谁能在一个月吃掉那么多烤长毛象呢？

随着人类进入电脑时代，电脑磁盘里存储的数据量和今天相比仍然是十分小的。尽管如此，随着数据量的不断增长，记录的长度和数量也在随之增长。

计算机存储数据的能力每十年都会有突飞猛进的增长。随着CPU处理能力以及硬盘存储量的迅猛增长，世界已经被彻底改变。超级计算机CPU的处理速度已经达到每秒中千的五次方次。数据库文件和索引的大小也已增长为terabyte (trillion)太(万亿)数量级。记录的长度和数量在增加，索引的键长度也需要增加 。索引对于数据库来说非常重要，所以对它的大小的限制应该逐渐提升。

IBM I 5.4 32K 索引

IBM i上集成的数据库也发生了很大的变化。在IBM i 5.3上，索引的键最大长度不能超过2000字节。 在IBM I 5.4上，记录描述符长度为3字节和4字节的索引可以包含的记录数最大上限分别为4GB和1TB. 记录描述符长度为4字节的索引键的最大长度从2000字节增加为32766字节。本文把32766字节这个值简称为32K. 文件描述符长度为3字节的索引键值长度最大仍为2000字节，键最多为120个。

访问路径access path(IBM i系统术语)，或者说索引，描述了记录是按照什么样的顺序被读取的。本文中的“索引”是指存储键的机器索引，是个容器。这不应该和“SQL索引”混淆，SQL索引是一个包含了索引容器的逻辑文件。

IBM i上集成度数据库有多种方式可以创建索引:

创建物理文件(CRTPF)和创建逻辑文件(CRTLF)命令

创建物理文件(CHGPF)命令来更改物理文件

添加物理文件的约束(ADDPFCST)，增加主键、唯一键或者引用约束

SQL CREATE INDEX语句

SQL ALTER TABLE语句，增加主键、唯一键或者引用约束

SQL CREATE TABLE语句，创建一个带有主键、唯一键或应用约束的表

这篇文章介绍了过去用来创建32K索引的方法。无论用什么样的方式去创建索引，最后创建出的索引都是相同的。大多数的IBM i用户对CRTPF、CRTLF、CHGPF和ADDPFCST命令都非常熟悉。我们对用每一个命令来创建32K索引都会提供一个简单的例子。

CRTPF和CRTLF命令

CRTPF命令和图1中的DDS可以用来创建一个用户指定顺序的带有32K索引的物理文件。

CRTPF FILE(CUST_LIB/CUST_ORD) SRCFILE(CUST_LIB/DDS)

  

图1

CRTLF命令和图2中的DDS可以用来创建一个带有32K索引的逻辑文件。这个逻辑文件是基于一个物理文件的。在图2的示例中，物理文件的名字在PFILE参数中给出。

图2

CHGPF命令

另一种在一个已经存在的物理文件上增加索引的方式是通过DDS和CHGPF命令。首先，创建一个不包含键的物理文件(参见图3).

CRTPF FILE(CUST_LIB/CUST_ORDPF) SRCFILE(CUST_LIB/DDS)

CHGPF FILE(CUST_LIB/CUST_ORDPF) SRCFILE(CUST_LIB/DDS)
SRCMBR(CHGFMT)

执行CHGPF后，CUST_ORDPF会带有一个32K的索引。

图3

图4

ADDPFCST命令

还有一种在一个已经存在的物理文件上增加索引的方式是通过ADDPFCST命令给物理文件添加一个约束。在这个例子中，我们假设物理文件PRN_FILE1, PRN_FILE2 和 DEP_FILE已经通过图1中的DDS创建好了。下面的命令在PRN_FILE1的四个域上创建了32K的唯一约束。

ADDPFCST FILE(CUST_LIB/PRN_FILE1 TYPE(*UNQCST)
KEY(CUST_NUM CUST_ADR CUST_ACT ORDR_NUM)
CST(CST_UNIQUE)

或者，用户可以在物理文件上添加一个32K的主键约束。

ADDPFCST FILE(CUST_LIB/PRN_FILE2 TYPE(*PRICST)
KEY(CUST_NUM CUST_ADR CUST_ACT ORDR_NUM)
CST(PRI_UNIQUE)

用户可以通过ADDPFCST命令给物理文件增加引用约束。引用约束是引用父文件中的主键或者唯一键。下面的例子展示了在DEP_FILE中创建一个32K的引用约束索引。

ADDPFCST FILE(CUST_LIB/DEP_FILE) TYPE(*REFCST)
KEY(CUST_NUM CUST_ADR CUST_ACT ORDR_NUM)
CST(REFCSTCUST_NUM) PRNFILE(CUST_LIB/PRN_FILE1)
PRNKEY(CUST_NUM CUST_ADR CUST_ACT ORDR_NUM)

父文件是CUST_LIB/PRN_FILE1，其中CUST_NUM, CUST_ADR, CUST_ACT 和 ORDR_NUM是父键。子文件是CUST_LIB/DEP_FILE，CUST_NUM, CUST_ADR, CUST_ACT and ORDR_NUM是引用键。引用键和父键上都被创建了32K的索引。

前面介绍的CL命令都可以用来创建32K索引。另外，用户也可以通过SQL语句来创建32K索引。

SQL CREATE INDEX语句

SQL 索引是通过SQL CREATE INDEX语句创建的。由于列可能会被设置一些属性或者包含空值，索引的最大长度可能会小于32K。

例如，如下的表:

CREATE TABLE CUST_LIB/CUST_ORDER1
(CUST_NUM CHAR (10000) NOT NULL,
CUST_ADR CHAR (10000) NOT NULL,
CUST_ACT CHAR (10000) NOT NULL,
ORDR_NUM CHAR (2766) NOT NULL)

表中记录的长度为32K(32766)，因此，能够创建的长度最长的索引也是32K。下面的CREATE INDEX语句会内构件一个32K的索引。

CREATE UNIQUE INDEX CUST_LIB/CUST_ORD1 ON CUST_LIB/CUST_ORDER1
(CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)

通过DSPFD命令可以查看CUST_LIB/CUST_ORD1这个索引的长度。(如图5所示)

图5

下面例子中表的列是允许空值的。

CREATE TABLE CUST_LIB/CUST_ORDER2
(CUST_NUM CHAR ( 10000),
CUST_ADR CHAR ( 10000),
CUST_ACT CHAR ( 10000),
ORDR_NUM CHAR ( 2764))

在这个表上创建索引的最大长度为32764字节，因为记录是可以包含空值的。如果ORDR_NUM这个列的长度增加1个字节，CREATE TABLE语句会执行成功，但是CREATE INDEX语句会失败:

CREATE UNIQUE INDEX CUST_LIB/CUST_ORD2 ON CUST_LIB/CUST_ORDER2
(CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)

通过DSPFD命令可以查看CUST_LIB/CUST_ORD2这个索引的长度。(如图6所示)

图6

SQL ALTER TABLE语句

下面的ALTER TABLE语句展示了如何通过添加约束的形式来创建32K索引。这个例子会使用如下的表:

CREATE TABLE CUST_LIB/PRN_FILE1
(CUST_NUM CHAR (10000) NOT NULL,
CUST_ADR CHAR (10000) NOT NULL,
CUST_ACT CHAR (10000) NOT NULL,
ORDR_NUM CHAR (2766) NOT NULL)

CREATE TABLE CUST_LIB/PRN_FILE2
(CUST_NUM CHAR (10000) NOT NULL,
CUST_ADR CHAR (10000) NOT NULL,
CUST_ACT CHAR (10000) NOT NULL,
ORDR_NUM CHAR (2766) NOT NULL)

CREATE TABLE CUST_LIB/DEP_FILE1
(CUST_NUM CHAR (10000) NOT NULL,
CUST_ADR CHAR (10000) NOT NULL,
CUST_ACT CHAR (10000) NOT NULL,
ORDR_NUM CHAR (2766) NOT NULL)

下面的两个ALTER TABLE语句分别创建主键和唯一键约束，每一个语句都会创建一个32K索引。

ALTER TABLE CUST_LIB/PRN_FILE1 ADD CONSTRAINT AT_PRIKEY
PRIMARY KEY (CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)

ALTER TABLE CUST_LIB/PRN_FILE2 ADD CONSTRAINT AT_UNQCST UNIQUE (CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)

下面的ALTER TABLE语句通过创建引用约束(外键)的形式创建了一个32K索引。它引用了父文件PRN_FILE1中通过创建主键而建立的32K索引。

ALTER TABLE CUST_LIB/DEP_FILE1 ADD CONSTRAINT AT_REFCST
FOREIGN KEY (CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)
REFERENCES CUST_LIB/PRN_FILE1
(CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)
ON DELETE NO ACTION ON UPDATE NO ACTION

SQL CREATE TABLE语句

   CREATE TABLE语句也可以用来创建32K索引。语法和ALTER TABLE的例子类似，不同的是ALTER TABLE中需要两步而CREATE TABLE只要执行一条语句这一步就能实现。下面的CREATE TABLE语句创建了一个表，这个表上带有一个32K的主键索引，通过创建唯一键和外键来创建32K索引的语法和这个例子是十分相似的:

CREATE TABLE CUST_LIB/CUST_ORDER
(CUST_NUM CHAR (10000 ) NOT NULL WITH DEFAULT,
CUST_ADR CHAR (10000 ) NOT NULL WITH DEFAULT,
CUST_ACT CHAR (10000 ) NOT NULL WITH DEFAULT,
ORDR_NUM CHAR (2766 ) NOT NULL WITH DEFUALT,
CONSTRAINT CRT_PRIKEY_CST
PRIMARY KEY (CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM))

通过DSPFD命令可以查看CUST_LIB/CUST_ORD这张表、表上的约束，也可以看到索引的长度为32K。(如图7所示)

  

图7

SQL API – QSQGNDDL

为了方便起见，通过QSQGNDDL API可以生成任何SQL 索引的CREATE INDEX的语句。这个API的输入是一个SQL索引或一个带有索引的逻辑文件，输出相应的SQL CREATE INDEX语句。

缩短响应时间

这篇文章介绍了如何通过不同的命令和方式来创建32K索引。把对索引长度的最大限制从2000字节提升为32K字节可以给用户更大的灵活性，用户的索引的键的长度可以更长，有利于用户管理数据以及提高了对长度更长的列的查询的性能。通过使用索引而不是线性查找，可以大幅减少查询的次数并降低应用程序的响应时间。响应时间的减少对数据库管理人员是一个大好的消息，创建32K的索引对实现这个目标大有帮助。

即使是我们的祖先都试图使用原始的索引，虽然他们是为了管理剑齿虎 — 一个不同的用途。