Technical Blog Post
Abstract
创建IBM i上的32K索引
Body
原文链接:
http://www.ibmsystemsmag.com/ibmi/administrator/db2/Creating-32K-Indexe…早在远古时期,索引已经被广泛用于组织和检索数据。史前时代的穴居人用原始的索引管理长毛象(猛犸象)和剑齿虎。这些索引非常小 — 谁能在一个月吃掉那么多烤长毛象呢?
随着人类进入电脑时代,电脑磁盘里存储的数据量和今天相比仍然是十分小的。尽管如此,随着数据量的不断增长,记录的长度和数量也在随之增长。
计算机存储数据的能力每十年都会有突飞猛进的增长。随着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) 执行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) 或者,用户可以在物理文件上添加一个32K的主键约束。 ADDPFCST FILE(CUST_LIB/PRN_FILE2 TYPE(*PRICST) 用户可以通过ADDPFCST命令给物理文件增加引用约束。引用约束是引用父文件中的主键或者唯一键。下面的例子展示了在DEP_FILE中创建一个32K的引用约束索引。 ADDPFCST FILE(CUST_LIB/DEP_FILE) TYPE(*REFCST) 父文件是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 表中记录的长度为32K(32766),因此,能够创建的长度最长的索引也是32K。下面的CREATE INDEX语句会内构件一个32K的索引。 CREATE UNIQUE INDEX CUST_LIB/CUST_ORD1 ON CUST_LIB/CUST_ORDER1 通过DSPFD命令可以查看CUST_LIB/CUST_ORD1这个索引的长度。(如图5所示) 图5 下面例子中表的列是允许空值的。 CREATE TABLE CUST_LIB/CUST_ORDER2 在这个表上创建索引的最大长度为32764字节,因为记录是可以包含空值的。如果ORDR_NUM这个列的长度增加1个字节,CREATE TABLE语句会执行成功,但是CREATE INDEX语句会失败: CREATE UNIQUE INDEX CUST_LIB/CUST_ORD2 ON CUST_LIB/CUST_ORDER2 通过DSPFD命令可以查看CUST_LIB/CUST_ORD2这个索引的长度。(如图6所示) 图6 SQL ALTER TABLE语句 下面的ALTER TABLE语句展示了如何通过添加约束的形式来创建32K索引。这个例子会使用如下的表: CREATE TABLE CUST_LIB/PRN_FILE1 CREATE TABLE CUST_LIB/PRN_FILE2 CREATE TABLE CUST_LIB/DEP_FILE1 下面的两个ALTER TABLE语句分别创建主键和唯一键约束,每一个语句都会创建一个32K索引。 ALTER TABLE CUST_LIB/PRN_FILE1 ADD CONSTRAINT AT_PRIKEY 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 SQL CREATE TABLE语句 CREATE TABLE语句也可以用来创建32K索引。语法和ALTER TABLE的例子类似,不同的是ALTER TABLE中需要两步而CREATE TABLE只要执行一条语句这一步就能实现。下面的CREATE TABLE语句创建了一个表,这个表上带有一个32K的主键索引,通过创建唯一键和外键来创建32K索引的语法和这个例子是十分相似的: CREATE TABLE CUST_LIB/CUST_ORDER 通过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的索引对实现这个目标大有帮助。 即使是我们的祖先都试图使用原始的索引,虽然他们是为了管理剑齿虎 — 一个不同的用途。
SRCMBR(CHGFMT)
KEY(CUST_NUM CUST_ADR CUST_ACT ORDR_NUM)
CST(CST_UNIQUE)
KEY(CUST_NUM CUST_ADR CUST_ACT ORDR_NUM)
CST(PRI_UNIQUE)
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_NUM CHAR (10000) NOT NULL,
CUST_ADR CHAR (10000) NOT NULL,
CUST_ACT CHAR (10000) NOT NULL,
ORDR_NUM CHAR (2766) NOT NULL)
(CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)
(CUST_NUM CHAR ( 10000),
CUST_ADR CHAR ( 10000),
CUST_ACT CHAR ( 10000),
ORDR_NUM CHAR ( 2764))
(CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)
(CUST_NUM CHAR (10000) NOT NULL,
CUST_ADR CHAR (10000) NOT NULL,
CUST_ACT CHAR (10000) NOT NULL,
ORDR_NUM CHAR (2766) NOT NULL)
(CUST_NUM CHAR (10000) NOT NULL,
CUST_ADR CHAR (10000) NOT NULL,
CUST_ACT CHAR (10000) NOT NULL,
ORDR_NUM CHAR (2766) NOT NULL)
(CUST_NUM CHAR (10000) NOT NULL,
CUST_ADR CHAR (10000) NOT NULL,
CUST_ACT CHAR (10000) NOT NULL,
ORDR_NUM CHAR (2766) NOT NULL)
PRIMARY KEY (CUST_NUM, CUST_ADR, CUST_ACT, ORDR_NUM)
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
(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))
UID
ibm11146094