SQL Derived-key索引帮助你管理数据

Body

原文链接：

http://www.ibmsystemsmag.com/ibmi/july08/technicalcorner/21013p1.aspx

在“创建i5/OS上的32K索引”这篇文章中

(http://www.ibmsystemsmag.com/ibmi/administrator/db2/Creating-32K-Indexes-in-i5-OS/)，我们讲述了史前时代的穴居人在烤长毛象（猛犸象）的时候用索引来管理身上贴有标签的长毛象。现在，有了derived-key索引，每个人都可以用一种更成熟的方法来管理数据。可以说，前人的一小步，成就了后代的一大步。

创建SQL索引主要有两个功能。第一，它可以基于表列创建一个键并用来存取表中的数据, 这样存取数据的速度会更快。第二，它可以保证表中没有两个数据行具有完全相同的键值。实际上，一个SQL索引是一个包含了反应表中数据的键值的逻辑文件。在IBM i 6.1之前，索引中的键值必须是和表列的数据一样的。

IBM i 6.1改变了这种机制，在创建索引的时候可以用一个表达式而不仅仅是使用表列的名字，这个表达式是有一定的限制的，我们将在本文接下来的部分中进行介绍。“derived-key index” 或者说 “带有表达式的SQL index”并不是SQL标准中的一部分。Derived key索引特指在Power系统运行的IBM i平台上，包含了由对表列的组合、截取或翻译而构成的derived key的索引。在这篇文章中为了简单起见，我们把带有一个或多个表达式的索引称作derived-key index.

广义的Derived Key

想了解更为详细的介绍，请参阅SQL Reference. (http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafz.pdf). 使用表达式的一种方法是使用由一系列操作数和操作符组合成的算术表达式，其他类型的表达式还有：

COL1 * COL2 AS MYCOL

UPPER(COL3)

A literal such as 1

CAST(COL1*COL2 AS MYUDT)

值得注意的是，AS可以用来给表达式命名。如果没有使用AS为表达式命名，系统会为这个表达式指定一个名字。另外，前面第四个例子是向用户自定义类型(UDT) 的MYUDT进行的转换。我们会在后面的文章中介绍在derived-key索引中使用UDF的一些限制。

示例代码1是一个创建表和derived-key索引的例子。其中还包含了对索引执行DSPFD命令以显示创建该索引的SQL语句，同时也包括了DSPFFD命令以显示索引的键COLX, COL3和COLZ的描述。

CREATE TABLE LIB1/T1

  (COL1 INT NOT NULL WITH DEFAULT,

   COL2 INT NOT NULL WITH DEFAULT,

   COL3 INT NOT NULL WITH DEFAULT,

   COL4 CHAR (10 ) NOT NULL WITH DEFAULT,

   COL5 CHAR (10 ) NOT NULL WITH DEFAULT,

   COL6 CHAR (10 ) NOT NULL WITH DEFAULT)

CREATE INDEX LIB1/IX1 on LIB1/T1                        

   (COL1*COL2 as COLX,

    COL3,

    UPPER(COL4),

    COL5||COL6 as COLZ)

=============================================================

DSPFD of IX1 gives CREATE INDEX text information

SQL Index create statement  . . . . . . . . :                        

  CREATE INDEX IX1 ON LIB1.T1 ( COL1 * COL2 AS COLX , COL3 ,

   UPPER ( COL4 ) , COL5 || COL6 AS COLZ )                                                                    

=============================================================

DSPFFD of IX1 gives field information

           Data        Field  Buffer    Buffer

Field      Type       Length  Length  Position

COLX       BINARY       9  0       4         1

  Derived field text  . . . . . . . . . . . : 

    COL1 * COL2                  

COL3       BINARY       9  0       4         5

IXCOL00001 CHAR           10      10         9

  Coded Character Set Identifier  . . . . . :     37

  Derived field text  . . . . . . . . . . . : 

    UPPER ( COL4 )                            

COLZ       CHAR           20      20        19

  Coded Character Set Identifier  . . . . . :     37

  Derived field text  . . . . . . . . . . . : 

COL5 || COL6  

示例代码1

AS语句中为表达式指定的名称将会出现在记录格式中。SQL索引可能会使用一个SQL表达式和/或表列名的组合，正如示例代码1中的COL3。而UPPER（COL4）中并没有使用AS，系统为这个表达式自动生成IXCOL00001这个名字并体现在记录格式中。在AS语句中直接使用已存在的表列的名字命名表达式是不正确的。例如，COL1* COL2 AS COL3是不正确的，因为COL3已经是表的一个列的列名。

UPPER（COL4）使得在IXCOL00001索引中，COL4列中的所有小写字母都被改为大写。

COL5和COL6串联后的结果COLZ的长度为20个字符。

对UPPER内置函数以及表达式使用的例子

示例代码2展示了在一个实际应用中如何使用derived-key索引的例子。这是一个客户-订单的应用，表中包含了所有的订单信息。表中的数据有客户的名字、订货量以及单价。

CREATE TABLE MYCUST.CUST_ORDER

  (CUSTOMER_NAME FOR COLUMN CUSTNAME CHAR ( 100) NOT NULL WITH DEFAULT,

    WIDGETS_NUMBER FOR COLUMN WIDGET# DEC (10, 0) NOT NULL WITH DEFAULT,

    WIDGET_UNIT_COST FOR COLUMN WIDGETCOST DEC (5, 2) NOT NULL WITH DEFAULT)            

CREATE INDEX MYCUST.CUST_INFO ON MYCUST.CUST_ORDER

  ((WIDGET# * WIDGETCOST) AS TOTALSALE , UPPER(CUSTNAME) AS UPCUSTNM)                  

=============================================================

DSPFFD of SQL Index CUST_INFO         

Field Level Information                                                  

           Data        Field  Buffer    Buffer        Field    Column  

Field      Type       Length  Length  Position        Usage    Heading 

TOTALSALE  PACKED      15  2       8         1        Input    TOTALSALE

Derived field text  . . . . . . . . . . . :                          

    ( WIDGET# * WIDGETCOST )                                           

UPCUSTNM   CHAR          100     100         9        Input    UPCUSTNM

  Coded Character Set Identifier  . . . . . :     37                   

  Derived field text  . . . . . . . . . . . :                          

UPPER ( CUSTNAME )

示例代码2

这个derived-key索引包含一个根据订货量以及单价计算出销售总价的表达式。在索引中所有的客户名字都会被转换为大写以避免因表中数据的大小写而引起的混淆。在对表的查询中，可能会得到Acme Company1, ACME CoMPAny1, and AcMe company1几种不同形式的客户名字，尽管它们都是同一个客户。但索引中只会包含ACME COMPANY1。

示例代码2中展示了创建表、创建索引以及DSPFFD。为了表明索引是如何被使用的，示例代码3则展示了在插入数据后CUST_ORDER表中的数据。

INSERT INTO MYCUST.CUST_ORDER

  (WIDGETS_NUMBER, WIDGET_UNIT_COST, CUSTOMER_NAME)

  VALUES(32, 7.32, 'American Resale LTD')

=============================================================

SELECT WIDGETS_NUMBER, WIDGET_UNIT_COST, CUSTOMER_NAME

  FROM MYCUST.CUST_ORDER                                         

....+....1....+....2....+....3....+....4....+....5....+....6

WIDGETS_NUMBER   WIDGET_UNIT_COST  CUSTOMER_NAME           

            32          7.32       American Resale LTD     

********  End of data  ******** 

示例代码3

直接存取索引中的内容并不容易，除非写一个程序调用native I/O接口来访问逻辑文件（索引）。尽管derived-key索引的记录格式被标记为是只写的，一个包含了对native I/O接口调用的程序仍然可以同时对索引进行读取和写入。

存取索引中内容的另一个较为简单的方法是调用CPYF命令把索引中的数据复制到一个物理文件中，然后就可以通过DSPPFM命令或者SQL SELECT语句查询该文件来查看文件中的数据。

示例代码4对这种方法进行了说明。它验证了表达式的最终结果，即订货量与单价的乘积，并且客户的名字使用大写字母显示的。

CPYF FROMFILE(MYCUST/CUST_INFO)

           TOFILE(MYCUST/PFCUSTINFO) CRTFILE(*YES)

===========================================================

SELECT * FROM MYCUST/PFCUSTINFO

....+....1....+....2....+....3....+....4....+

           TOTALSALE   UPCUSTNM           

              234.24   AMERICAN RESALE LTD  

********  End of data  ******** 

示例代码4

为了让查询能够使用derived-key索引，可以在SELECT查询的ORDER BY或GROUP BY语句中指定示例代码4中的表达式。你也可以通过调用STRDBG命令来判定是否使用了索引，然后执行SELECT查询并在job log中查找CPI4328这个消息，来判断在查询的时候是否使用了该索引。或者通过Database monitor阿也可以监视索引的使用情况。

示例代码5是一个带有ORDER BY的SELECT查询，JOB LOG中的CPI4328这个消息表示查询使用了derived-key索引。

SELECT * FROM MYCUST.CUST_ORDER 

  ORDER BY WIDGET# * WIDGETCOST 

============================================================

Message ID . . . . . . :   CPI4328       Severity . . . . . . . :   00     

Message . . . . :   Access path of file CUST_INFO was used by query.       

Cause . . . . . :   Access path for member CUST_INFO of file CUST_INFO in  

  library MYCUST was used to access records from member CUST_ORDER of file 

  CUST_ORDER in library MYCUST.

示例代码5

优点

使用derived-key索引的优点是双重的。首先是性能。在查询前进行数据的计算和数据转换（参见示例代码5）可以避免在运行时执行这些任务，因此该查询执行得更快。

另外，你不再需要在不同的SELECT语句、函数、存储过程或者程序中进行这些计算或数据转换。这些都可以放在derived-key索引中进行。这样可以把开发的消耗将至最低并且减少因为在多个地方使用这些计算或数据转换而带来的时间和精力上的浪费。

记录格式

IBM i数据库新增加了一个关键字用来适应derived-key索引，这样做的目的是让你可以指定记录格式中应该包含哪些列。

传统上，表中所有的列都被包含在索引的记录格式中。这也是RCDFMT关键字的缺省值。但是，因为索引记录格式有32K的大小限制，而且随着不断创建derived-key索引，新的列被增加到记录格式中，记录格式也会因此变长，所以记录格式有可能会包括所有表中的列和因为创建derived-key索引而增加的增加的列，这样记录格式就很有可能会超过32K的限制。因此，你可以选择在记录格式中加入表的所有列，或者只加入创建的索引的key列，或者只是加入你指定的列。

使用上的一些限制

Derived-key索引中表达式的使用规则与check constraints类似。更详细的信息请参考SQL Reference。下面是一些比较有趣的规则：

Derived-key索引不允许在表达式中使用UDF(用户自定义函数)。但是，可以使用cast函数把数据转换成UDT。

内置函数是支持的，但是UDF不允许在表达式中使用。

任何的SQL derived-key索引都不能被保存成6.1版本之前的版本。

Derived-key索引和check constraint在使用上有同样的限制。

主键(Primary Key)、唯一(Unique)和引用约束(referential constraint)不能使用或共用SQL Derived-key索引。这几种约束不支持SQL Derived Key的表达式。

SQL derived-key 索引是一种能够显著提高性能的工具，并且易于使用。这一功能使得编程和使用比以往更加的容易。现在就开始使用derived-key索引并享受这个IBM i数据库的新功能吧！

好啦，烤炉上的长毛象已经烤好了。