DB2中多种常用功能的解决方法(1)

简介

关系模型是一件美好事物。对它妥协是牵强附会，就象给米开朗琪罗的大卫扣上一顶棒球帽一样。然而，不属于纯关系模型的事物可能最终出现在您的数据库或应用程序中。如果您以修道士般的严肃态度看待关系誓约，那么不必继续阅读本文。对那些可能希望享受在狂野地带漫步的人而言，本文将带您到 RDBMS 的红灯区。请继续阅读本文以发现如何：

• 存储派生值
• 给每行编号以创建一个人工键
• 检索由 DB2® 生成的标识值
• 给结果集中的行编号
• 请求数据然后截断结果集
• 删除表中所有行而不记入日志
• 用视图和重命名表命令“删除”列
• 加宽 varchar 列
• 学习何时使用真的视图以及汇总表（名声不太好的实现视图）
• 学习声明的临时表与公共表表达式之间的差异

这些技术中的一些技术可以改进性能，就象您可能在 RDBMS 参考手册中看到的实用建议，它建议您使数据符合第四范式，然后在实现设计前做一些妥协以取得需要的性能。其它一些技术会使应用程序程序员的工作更简单，并且带来性能上意想不到的效果。

派生值

您可能在关于关系数据库的大学课程中的某处学到不要存储派生值。毕竟，可以在组装结果集时计算这些值，从而避免数据库中的冗余数据，并获得正确答案。DB2 在版本 7 中引入生成的列有以下几个原因。

• 请求数据的用户可能正在使用您没有控制权的应用程序。如果他们正在使用只需移动和点击鼠标就可以完成工作的应用程序进行 SELECT * 操作，他们可能没有意识到他们真正希望看到的是 COMPENSATION，并且必须将 COMMISSION 和 SALARY 列中的值相加。一个生成的列允许您存储这个值，并保持它的准确性： clearcase/" target="_blank" >cccccc border=1>

  create table employee (name char(10), salary dec(10,2), commission dec(10,2), compensation dec(11,2) generated always as (commission + salary))

  通过下面的 SQL 语句来保持 COMPENSATION 列的准确性：

  insert into employee (name, salary, commission) values ('Blair',5,10)

  update employee set salary=0

• 您需要唯一地标识每一行。我们将在稍后讨论这一点。
  
  
• 要求不区分大小写来创建索引，接下来就介绍这一点。

区分大小写

区分大小写是功能强大的，而且如果 RDBMS 知道 Greenland 与 greenland 不匹配，它会搜索得更快。然而，用户如果提交对“Macinnis”的搜索，他们可能实际上希望您的应用程序返回“MacInnis”。对于名称搜索，您可能要考虑在 NAME 列上创建一个索引。然而，DB2 索引中的值也是区分大小写的。让 MacInnis=Macinnis 很简单，只要使用 UPPER 或 UCASE 函数即可：

SELECT NAME FROM EMPLOYEE WHERE UPPER(NAME) = 'MACINNIS'

但是，这会强制进行表扫描，而且您得不到索引的好处。这就是引入生成的列的用途所在：如果标准访问方法是关于名称的搜索，那么使用生成的列来以大写格式存储名称：

CREATE TABLE EMPLOYEE (NAME VARCHAR(10),

        


    NAME_UP

        


        GENERATED ALWAYS AS (UPPER(name)))



      

现在在这个列的大写版本上创建索引：

CREATE INDEX NAME_IND ON EMPLOYEE ( NAME_UP )

该查询可以获得索引的好处，并避免了表扫描：

SELECT NAME FROM EMPLOYEE WHERE UPPER(NAME) = 'MACINNIS'

让我们看看如何用生成的列来枚举行。我们为什么要给行编号？关系理论告诉我们行与列没有内在的顺序：您可以在请求数据时指定顺序。但人们喜欢给事物编号，从书中的页号到运动衫上的号码。您可能知道在计算机科学中数据被看成表格式关系模型。您的用户有多少学会查看 Lotus 和 Excel 电子表格中的表格式数据（屏幕左边有向下递增的行号）呢？大多数关系数据库管理系统都有内部 RID（行标识）或 TID（元组标识）。OS/390® 上的 DB2 和 Oracle 将这一点具体化，使程序无需知道内容就可以方便地标识行。我们没有具体化 Windows／UNIX／OS/2 上 DB2 的行标识，因为我们允许它改变：潜在主键中的一个危险特性。DB2 的确有其它方法将一列作为人工主键使用。

在您借助任何这些唯一标识每一行的基本方法之前，请尽力找到真实的主键：问自己这个问题：“如果我们把每一行都写在纸上，应如何唯一标识它；假定客户或供应商打电话询问状态 — 我们怎样才能找到他们所询问的数据？”如果您在每张纸上都打上日期和时间戳记，那么这就是主键。

生成行标识

让我们从由其它数据库迁移到 DB2 的应用程序开始。如果移自 SQL Server，您可能厌倦了关于关系纯洁性的说教并希望了解 IDENTITY。下面是如何用 DB2 v7 创建 IDENTITY 列：

CREATE TABLE T1

        


    (C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY

        


        (START WITH 10),

        


          C2 INTEGER)



      

还有在内存中高速缓存标识值的选项，这使插入更快，但如果您的系统在生成 IDENTITY 值时遭受硬件或软件崩溃，那么将在标识序列中留下间隔。缺省情况是一次增加整数 1，但您也可以按其它值（2 和 10 等）增加。插入后，您会对生成的值自然地产生好奇。为了应用程序的下一段逻辑，您可能需要知道这个值。在发行说明（Windows 上 x:\sqllib\release.txt）中记录的名为 IDENTITY_VAL_LOCAL() 的函数可为您检索这个值。

IDENTITY 在每个表中是唯一的。那些 Oracle 迷将很高兴得知 DB2 的版本 7，修订包 3 将把 SEQUENCE 列带入 DB2。序列在整个数据库中是独一无二的 — 这对于在多个表中使用的值很有用。您也可以在序列中循环以重用这些值。SEQUENCE 和 IDENTITY 不是数据类型：它们使用象 SMALLINT、INTEGER 或小数位是零的 DEC 那样的现有数据类型。INT 和 BIGINT 是最好的选择，它们能给您良好的性能和适当的数值范围。还允许负值。

生成人工主键还有其它方法。如果一次只有一位用户访问表（并且一次只插入一行），则触发器很不错。将您的主键列定义为缺省非空值，这样当在 INSERT 中没有指定它时，它就得到一个虚设的值（触发器将重写这个虚设的值）：

CREATE TRIGGER AutoIncrement NO CASCADE BEFORE

        


   INSERT ON Foobar

        


     REFERENCING NEW AS n

        


          FOR EACH ROW MODE DB2SQL  SET (n.col1) =

        


     (SELECT COALESCE(MAX(col1),0) + 1 FROM Foobar )



      

DB2 还有一个名为 GENERATE_UNIQUE 的函数。这个函数将节点号（用于多分区数据库）与时间戳记结合，因此它可以与企业扩展版本（EEE）一起使用。IDENTITY 和 SEQUENCE 在 DB2 的下一个主要版本出现前还不能与 EEE 一起使用。GENERATE_UNIQUE 有两个缺点：数据类型（CHAR(13) FOR BIT DATA）不是按顺序递增，并且不象数值数据类型那样易于使用。

更简单的解决方案是标量子查询表达式：

INSERT INTO Foobar (key_col, ...)

        


     VALUES (COALESCE((SELECT MAX(key_col) FROM Foobar) +1, 0) ...)



      

获得一屏数据

这些方法对于那些在数据库和应用程序投入生产以前有机会进行一些设计工作的模式和应用程序来说是不错的。但您还记得那两个以 A（ARIES（航班订票环境仿真）和 ACID（原子性、一致性、隔离和持久性））开头的 4.5 字母单词吗？如果您预定了航班，那么您希望他们在您到达机场时记得这回事。这就是持久性：有用的数据是持久的。这意味着即使您定义了一个好的主键，有些人可能会查询结果集的“前二十行”，而不管结果集中有多少行。更糟的情况是有人要求您显示第 21 行到 40 行。但等一下，您会提出异议，关系表中的行没有顺序！对于希望在他们的 Netscape 浏览器中一次看到二十行的用户而言，您就好象在说冰岛语。DB2 允许您实时地给结果集排序，并可以从该结果集的开始或结尾部分提取任意数量的行：

SELECT NAME FROM ADDRESS

        


    ORDER BY NAME

        


        FETCH FIRST 10 ROWS ONLY



      

SELECT NAME FROM ADDRESS

        


    ORDER BY NAME DESC

        


        FETCH FIRST 10 ROWS ONLY



      

ORDER BY 将强制在内存中对整个结果集进行排序，所以，为了提高 DB2 服务器性能，我们不这么做（尽管只向客户机发送 10 行可能会提高网络性能）。如果您不关心顺序并且只想知道至少有 10 行符合结果集，则清除 ORDER BY 以省去 DB2 服务器上的排序：

SELECT NAME FROM ADDRESS

        


        FETCH FIRST 10 ROWS ONLY





      

于是现在我们已看到您给行编号并且任意选择了一个子集。假设我们因某些性能上的好处而给行编号，这必将破坏关系模型。我们几乎完全妥协了，并且已经犯了关系七宗罪中的六宗。还有一条关系誓约您没有触犯：让我们实时地给行编号，牺牲掉性能和关系纯洁性吧。我们如何证明这样做的正确性呢？在因特网上谴责它吧。

原文转自：http://www.ltesting.net