【導讀】描述了多種常用功能的解決方法，而這些功能并不完全符合關系誓約。簡介關系模型是一件美好事物。對它妥協是牽強附會，就象給米開朗琪羅的大衛扣上一頂棒球帽一樣。然而，不屬于純關系模型的事物可能最終出現在您的數據庫或應用程序中。假如您以修道士般的嚴厲態度看待關系誓約，那么不必繼續閱讀本文。對那些可能希望享受在狂野地帶漫步的人而言，本文將帶您到 RDBMS 的紅燈區。請繼續閱讀本文以發現如何：存儲派生值給每行編號以創建一個人工鍵檢索由 DB2® 生成的標識值給結果集中的行編號請求數據然后截斷結果集刪除表中所有行而不記入日志用視圖和重命名表命令“刪除列加寬 varchar 列學習何時使用真的視圖以及匯總表（名聲不太好的實現視圖）學習聲明的臨時表與公共表表達式之間的差異這些技術中的一些技術可以改進性能，就象您可能在 RDBMS 參考手冊中看到的實用建議，它建議您使數據符合第四范式，然后在實現設計前做一些妥協以取得需要的性能。其它一些技術會使應用程序程序員的工作更簡單，并且帶來性能上意想不到的效果。派生值您可能在關于關系數據庫的大學課程中的某處學到不要存儲派生值。究竟，可以在組裝結果集時計算這些值，從而避免數據庫中的冗余數據，并獲得正確答案。DB2 在版本 7 中引入生成的列有以下幾個原因。請求數據的用戶可能正在使用您沒有控制權的應用程序。假如他們正在使用只需移動和點擊鼠標就可以完成工作的應用程序進行 SELECT * 操作，他們可能沒有意識到他們真正希望看到的是 COMPENSATION，并且必須將 COMMISSION 和 SALARY 列中的值相加。一個生成的列答應您存儲這個值，并保持它的準確性： 12345678下一頁 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_UPGENERATED 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 的確有其它方法將一列作為人工主鍵使用。 上一頁12345678下一頁 在您借助任何這些唯一標識每一行的基本方法之前，請盡力找到真實的主鍵：問自己這個問題：“假如我們把每一行都寫在紙上，應如何唯一標識它；假定客戶或供給商打電話詢問狀態 — 我們怎樣才能找到他們所詢問的數據？假如您在每張紙上都打上日期和時間戳記，那么這就是主鍵。生成行標識讓我們從由其它數據庫遷移到 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:sqllibelease.txt）中記錄的名為 IDENTITY_VAL_LOCAL() 的函數可為您檢索這個值。IDENTITY 在每個表中是唯一的。那些 Oracle 迷將很興奮得知 DB2 的版本 7，修訂包 3 將把 SEQUENCE 列帶入 DB2。序列在整個數據庫中是獨一無二的 — 這對于在多個表中使用的值很有用。您也可以在序列中循環以重用這些值。SEQUENCE 和 IDENTITY 不是數據類型：它們使用象 SMALLINT、INTEGER 或小數位是零的 DEC 那樣的現有數據類型。INT 和 BIGINT 是最好的選擇，它們能給您良好的性能和適當的數值范圍。還答應負值。生成人工主鍵還有其它方法。假如一次只有一位用戶訪問表（并且一次只插入一行），則觸發器很不錯。將您的主鍵列定義為缺省非空值，這樣當在 INSERT 中沒有指定它時，它就得到一個虛設的值（觸發器將重寫這個虛設的值）： 上一頁12345678下一頁 CREATE TRIGGER AutoIncrement NO CASCADE BEFOREINSERT ON FoobarREFERENCING NEW AS nFOR 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 ADDRESSORDER BY NAMEFETCH FIRST 10 ROWS ONLYSELECT NAME FROM ADDRESSORDER BY NAME DESCFETCH FIRST 10 ROWS ONLYORDER BY 將強制在內存中對整個結果集進行排序，所以，為了提高 DB2 服務器性能，我們不這么做（盡管只向客戶機發送 10 行可能會提高網絡性能）。假如您不關心順序并且只想知道至少有 10 行符合結果集，則清除 ORDER BY 以省去 DB2 服務器上的排序： 上一頁12345678下一頁 SELECT NAME FROM ADDRESSFETCH FIRST 10 ROWS ONLY于是現在我們已看到您給行編號并且任意選擇了一個子集。假設我們因某些性能上的好處而給行編號，這必將破壞關系模型。我們幾乎完全妥協了，并且已經犯了關系七宗罪中的六宗。還有一條關系誓約您沒有觸犯：讓我們實時地給行編號，犧牲掉性能和關系純潔性吧。我們如何證實這樣做的正確性呢？在因特網上譴責它吧。【導讀】描述了多種常用功能的解決方法，而這些功能并不完全符合關系誓約。向使用瀏覽器的客戶顯示公司數據顯然證實了違反對關系純潔性和性能推崇所作的承諾。您可以用 rownum 或 rank 函數實時地給結果集賦予行號。下面我們為用來記錄地址的表中的行排序，并選擇第 11 行到第 20 行。結果集由名稱和實時創建的名為 rn 的列（它給行編號）組成：SELECT * FROM (SELECT NAME, rownumber() OVER(ORDER BY NAME)AS rn FROM ADDRESS)AS tr WHERE rn BETWEEN 11 and 20rank 更為復雜，并且它答應您以排序的順序標識聯系，對于足球聯賽非常理想：create table football (team char(10), points int)insert into football values ('United', 20)insert into football values ('Arsenal', 20)insert into football values ('Liverpool', 10)select rank() over(order by points desc) as place,team, pointsfrom footballPLACE TEAM POINTS1 United 201 Arsenal 203 Liverpool 10清空表 — 無需通知日志記錄程序（截斷表）現在你已經得到了很多精巧的方法來處理你的數據了，我們再來學習一個小把戲。其他的數據庫產品有被稱為“截斷表的功能，即在不進行日志記錄的情況下刪除表中的所有數據，而保留表的結構（假如不想保留表結構，我們就使用 DROP TABLE 命令了）。假如想在 DB2 中得到這種功能，可以執行帶有 REPLACE 選項的 LOAD 命令，并使用一個 0 字節的文件作為導入數據源，由于 DB2 的 LOAD 操作是不做日志的，所以可以通過這個小騙局來達到我們的目的。 上一頁12345678下一頁 猜測游戲和鏡屋您的表很不錯 — 為什么要從視圖訪問它？這樣做有許多理由：列級別安全性：排除那些您不希望用戶在定義視圖的 SELECT 中看到的列。行級別安全性：除非您定義一個視圖，否則 Windows／UNIX／OS/2 上的 DB2 v7 不答應您限制對表中某些行的訪問（假如您希望限制對答應用戶看到的內容的更新，請記得加上 check 選項）：create view london_football asselect * from footballwhere team in ('Arsenal','Aston Villa')with check option設想這一點對于“人力資源應用程序的作用：用戶可以查看薪水在 $nn,nnn 以下的雇員，給他們加薪而加薪后的薪水不會在 $nn,nnn 以上。DROP COLUMN：DB2 不答應您刪除一個列。我可以想到您希望刪除列的三個理由：回收空間：假如您希望這樣做，可以導出您希望保存的數據，刪除那個表，用您需要的那些列重新創建表，然后裝入這個表。這是否代價高昂？當然是，但是回收空間需要這樣或者 REORG TABLE。這些本來就是代價高昂的操作。這個列不再是行的邏輯部分：例如，您意識到您的雇員可能有兩個地址，并且停止跟蹤雇員（employee）表中的地址（雇員表和雇員地址（employee_address）表之間現在有 n:m 關系）。在雇員表上創建一個不包含地址列的視圖。假如您真的要用新奇的方法，可以使用 RENAME TABLE 命令給基表一個新的名稱，然后將原始表名作為該視圖的名稱。您的視圖也可以連接雇員表中的有用列和從雇員地址獲得的地址。現在我們回到了關系的正道。列變寬了。假如它是 VARCHAR，那您運氣不錯。DB2 答應您將 VARCHAR 列最多加寬至表空間（tablespace）中定義的頁大小寬度（缺省的 4K 頁大小為 4,005，而在 32K 頁上最多為 32,672）： 上一頁12345678下一頁 create table t2 (col1 varchar(10))alter table t2 alter column col1 set data type varchar(12)我很喜歡這個視圖，所以我實現它假如派生列對您來說還不夠壞，整個派生表怎么樣？使它與基表中的數據匹配或不匹配（以及使每個 SELECT 成為潛在的錯讀）的能力又如何？Oracle 稱這些為實現的視圖。DB2 稱它們為自動匯總表，在非凡情況下稱為復制匯總表。假如經常被問到一個問題（SELECT MAX(ORDERS) FROM LEADS），或者經常組裝一個聚集（SELECT COUNT(FRANCHISES) FROM STORES WHERE STATE=’TEXAS’），那么或許值得將結果集存儲在磁盤上，這樣 DB2 就不必天天重新計算它二十次：非凡當幾天前的數據足以準確地支持基于查詢的決策時。讓我們從想知道哪個客戶訂購最多的貪婪的銷售經理開始。他們在名為 LEADS 的表中跟蹤這一項，推斷出客戶過去所下訂單的數目可能有助于確定哪些銷售線索最有可能變為真實的銷售。這個問題天天會被問幾次（假如您預感這正在發生并且需要驗證它，您可以使用名為 Query Patroller 的 DB2 工具查看來自用戶的查詢）。SELECT MAX() 通常需要一個表掃描，這會強制 DB2 查看表中的每一行。假如您有許多線索，則需要掃描許多行才能找到一個值。定義一個匯總表答應 DB2 將這個值存儲在磁盤上，這樣 DB2 只用讀一行就可以得到答案：create summary table leads_max(MAX_ORDERS) as (SELECT MAX(ORDERS) FROM LEADS )DATA INITIALLY DEFERREDREFRESH DEFERRED創建匯總表后，用這條命令填充它：REFRESH TABLE LEADS_MAX用戶不必了解匯總表。DB2 優化器會決定何時使用基本表，何時使用匯總表。請注重 REFRESH DEFERRED 子句：您正在告訴 DB2 舊數據在匯總表中是可接受的。這在您不需要準確答案或當前答案時是合適的。它適合構建一個業務計劃，但對于要怎樣存儲銀行余額，它就不適合了。請參閱 SQL Reference 中的非凡寄存器 CURRENT REFRESH AGE 以及 Administration Guide 中的“Creating a Summary Table一節，以了解在答案可以“足夠接近、無需精確時，如何為匯總表中的舊數據設置容忍度。 上一頁12345678下一頁 REFRESH DEFERRED 是總結只讀表上數據的理想選擇。多分區數據庫的非凡匯總表稱為復制匯總表。您將在 DB2 EEE 中使用它以在每個分區都有小型表（或只讀表）的副本。在 EEE 中，您通常將最大的表（稱為事實表）分布到所有的分區。大量使用的連接鍵（如客戶號碼）應該作為分區鍵使用。DB2 將數據進行散列處理以對它分區。這意味著較少使用的連接鍵（如國家／地區或部門）可能會以次優化方式分布。當您在多分區數據庫中連接數據時，與組合的連接更快（例如，CUSTOMER 和 COUNTRY 表中所有 COUNTRY 為 Argentina 的行都在同一分區）。假如 COUNTRY 不是分區鍵，這是不可能的。要獲得組合，您可以將較小的表限制在一個分區，然后創建一個將它復制到其它分區的復制匯總表。這一策略在所復制的表較小或很少有更改時奏效（假如您在經常更改國名的國家做生意的話，要避免這么做）。假如表確實很小（如各大洲的列表），不要費心去復制它：DB2 將把它傳送到所有分區并在連接期間將它保留在內存中。不要擔心通過名稱連接到副本：判定副本表何時可以提高性能是 DB2 的工作。通過使匯總表 REFRESH IMMEDIATE，可以將它們用于動態數據。這有比 REFRESH DEFERRED 更嚴格的規則，所以請仔細閱讀 SQL Reference。在首次創建匯總表之后，您仍必須使用 REFRESH TABLE 語句：CREATE SUMMARY TABLE LEADS_BY_STATE(NUM_LEADS, GRP_STATE)AS (SELECT COUNT(ORDERS), STATE FROM LEADS GROUP BY STATE)DATA INITIALLY DEFERREDREFRESH IMMEDIATEREFRESH TABLE LEADS_BY_STATE也可以這樣我們現在已研究了兩種視圖。作為標準視圖，視圖定義存儲在數據庫中（在 SYSCAT.VIEWS.TEXT 中）而數據只存儲在基表中。我們可以通過創建匯總表使得在這個數據上執行 SELECT 操作更快，這是以冗余數據為代價，它消耗更多磁盤空間并使得 INSERT、UPDATE 和 DELETE 更慢（或讓基表和匯總表不同步，至少在下一次刷新以前是這樣）。還有另一個極端：創建一個僅在數據庫連接期間存在的聚集，或者甚至和 SQL 語句的生命期一樣短。第一個稱為 DECLARED TEMPORARY TABLE，第二個稱為 COMMON TABLE EXPRESSION，也稱為 TEMPORARY RESULT TABLE。一個聲明的臨時表需要一個 USER TEMPORARY TABLESPACE，您可以用 CREATE TABLESPACE 命令創建它（請參閱 SQL Reference）。您可以將這個臨時表聲明為應用程序運行時數據的保留位置。DECLARE GLOBAL TEMPORARY TABLE table1(column1 INT, column2 INT)NOT LOGGED您用模式 SESSION 限定表，因為它屬于您連接到數據庫時創建的會話：INSERT INTO SESSION.TABLE1 VALUES (4,5)SELECT * FROM SESSION.TABLE1您可能希望這個臨時表與現有表匹配，因此您可以用現有表的 SELECT 語句填充它。假如這樣的話，使用 LIKE 創建它：DECLARE GLOBAL TEMPORARY TABLE TEMP_EMPLIKE EMPLOYEENOT LOGGEDINSERT INTO SESSION.TEMP_EMPSELECT * FROM EMPLOYEE當您斷開連接時，DB2 將刪除這個臨時表。對于某些更臨時的東西，DB2 支持公共表表達式，它答應您定義只存在于一條語句的表。公共表表達式還是另一個細微問題的答案：給一個不是以動詞開始的 SQL 語句命名：WITH COMPENSATION AS(SELECT SUM(SALARY+COMMISSION)AS TOTAL FROM EMPLOYEE)SELECT TOTAL FROM COMPENSATION您現在已被護送出紅燈區。公共表表達式并不違反關系原則：它不要求 DB2 存儲派生數據，也不添加人工列。假如一定要從這個故事引出一個寓意的話，假定用計算機解決一個問題有 n 種方法。一種方法可能成本最低，一種方法對您而言最快，一種方法對用戶而言最快，而另一種方法對于繼續您的設計以進行維護和添加新功能的開發人員而言最快。至于哪種選擇最好，就作為習題留給讀者吧。 上一頁12345678