【導讀】描述了多種常用功能的解決方法，而這些功能并不完全符合關(guān)系誓約。簡介關(guān)系模型是一件美好事物。對它妥協(xié)是牽強附會，就象給米開朗琪羅的大衛(wèi)扣上一頂棒球帽一樣。然而，不屬于純關(guān)系模型的事物可能最終出現(xiàn)在您的數(shù)據(jù)庫或應用程序中。假如您以修道士般的嚴厲態(tài)度看待關(guān)系誓約，那么不必繼續(xù)閱讀本文。對那些可能希望享受在狂野地帶漫步的人而言，本文將帶您到 RDBMS 的紅燈區(qū)。請繼續(xù)閱讀本文以發(fā)現(xiàn)如何：存儲派生值給每行編號以創(chuàng)建一個人工鍵檢索由 DB2® 生成的標識值給結(jié)果集中的行編號請求數(shù)據(jù)然后截斷結(jié)果集刪除表中所有行而不記入日志用視圖和重命名表命令“刪除列加寬 varchar 列學習何時使用真的視圖以及匯總表（名聲不太好的實現(xiàn)視圖）學習聲明的臨時表與公共表表達式之間的差異這些技術(shù)中的一些技術(shù)可以改進性能，就象您可能在 RDBMS 參考手冊中看到的實用建議，它建議您使數(shù)據(jù)符合第四范式，然后在實現(xiàn)設計前做一些妥協(xié)以取得需要的性能。其它一些技術(shù)會使應用程序程序員的工作更簡單，并且?guī)硇阅苌弦庀氩坏降男ЧＥ缮的赡茉陉P(guān)于關(guān)系數(shù)據(jù)庫的大學課程中的某處學到不要存儲派生值。究竟，可以在組裝結(jié)果集時計算這些值，從而避免數(shù)據(jù)庫中的冗余數(shù)據(jù)，并獲得正確答案。DB2 在版本 7 中引入生成的列有以下幾個原因。請求數(shù)據(jù)的用戶可能正在使用您沒有控制權(quán)的應用程序。假如他們正在使用只需移動和點擊鼠標就可以完成工作的應用程序進行 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您需要唯一地標識每一行。我們將在稍后討論這一點。要求不區(qū)分大小寫來創(chuàng)建索引，接下來就介紹這一點。區(qū)分大小寫區(qū)分大小寫是功能強大的，而且假如 RDBMS 知道 Greenland 與 greenland 不匹配，它會搜索得更快。然而，用戶假如提交對“Macinnis的搜索，他們可能實際上希望您的應用程序返回“MacInnis。對于名稱搜索，您可能要考慮在 NAME 列上創(chuàng)建一個索引。然而，DB2 索引中的值也是區(qū)分大小寫的。讓 MacInnis=Macinnis 很簡單，只要使用 UPPER 或 UCASE 函數(shù)即可：SELECT NAME FROM EMPLOYEE WHERE UPPER(NAME) = 'MACINNIS'但是，這會強制進行表掃描，而且您得不到索引的好處。這就是引入生成的列的用途所在：假如標準訪問方法是關(guān)于名稱的搜索，那么使用生成的列來以大寫格式存儲名稱：CREATE TABLE EMPLOYEE (NAME VARCHAR(10),NAME_UPGENERATED ALWAYS AS (UPPER(name)))現(xiàn)在在這個列的大寫版本上創(chuàng)建索引：CREATE INDEX NAME_IND ON EMPLOYEE ( NAME_UP )該查詢可以獲得索引的好處，并避免了表掃描：SELECT NAME FROM EMPLOYEE WHERE UPPER(NAME) = 'MACINNIS'讓我們看看如何用生成的列來枚舉行。我們?yōu)槭裁匆o行編號？關(guān)系理論告訴我們行與列沒有內(nèi)在的順序：您可以在請求數(shù)據(jù)時指定順序。但人們喜歡給事物編號，從書中的頁號到運動衫上的號碼。您可能知道在計算機科學中數(shù)據(jù)被看成表格式關(guān)系模型。您的用戶有多少學會查看 Lotus 和 Excel 電子表格中的表格式數(shù)據(jù)（屏幕左邊有向下遞增的行號）呢？大多數(shù)關(guān)系數(shù)據(jù)庫治理系統(tǒng)都有內(nèi)部 RID（行標識）或 TID（元組標識）。OS/390® 上的 DB2 和 Oracle 將這一點具體化，使程序無需知道內(nèi)容就可以方便地標識行。我們沒有具體化 Windows／UNIX／OS/2 上 DB2 的行標識，因為我們答應它改變：潛在主鍵中的一個危險特性。DB2 的確有其它方法將一列作為人工主鍵使用。 上一頁12345678下一頁 在您借助任何這些唯一標識每一行的基本方法之前，請盡力找到真實的主鍵：問自己這個問題：“假如我們把每一行都寫在紙上，應如何唯一標識它；假定客戶或供給商打電話詢問狀態(tài) — 我們怎樣才能找到他們所詢問的數(shù)據(jù)？假如您在每張紙上都打上日期和時間戳記，那么這就是主鍵。生成行標識讓我們從由其它數(shù)據(jù)庫遷移到 DB2 的應用程序開始。假如移自 SQL Server，您可能厭倦了關(guān)于關(guān)系純潔性的說教并希望了解 IDENTITY。下面是如何用 DB2 v7 創(chuàng)建 IDENTITY 列：CREATE TABLE T1(C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY(START WITH 10),C2 INTEGER)還有在內(nèi)存中高速緩存標識值的選項，這使插入更快，但假如您的系統(tǒng)在生成 IDENTITY 值時遭受硬件或軟件崩潰，那么將在標識序列中留下間隔。缺省情況是一次增加整數(shù) 1，但您也可以按其它值（2 和 10 等）增加。插入后，您會對生成的值自然地產(chǎn)生好奇。為了應用程序的下一段邏輯，您可能需要知道這個值。在發(fā)行說明（Windows 上 x:sqllibelease.txt）中記錄的名為 IDENTITY_VAL_LOCAL() 的函數(shù)可為您檢索這個值。IDENTITY 在每個表中是唯一的。那些 Oracle 迷將很興奮得知 DB2 的版本 7，修訂包 3 將把 SEQUENCE 列帶入 DB2。序列在整個數(shù)據(jù)庫中是獨一無二的 — 這對于在多個表中使用的值很有用。您也可以在序列中循環(huán)以重用這些值。SEQUENCE 和 IDENTITY 不是數(shù)據(jù)類型：它們使用象 SMALLINT、INTEGER 或小數(shù)位是零的 DEC 那樣的現(xiàn)有數(shù)據(jù)類型。INT 和 BIGINT 是最好的選擇，它們能給您良好的性能和適當?shù)臄?shù)值范圍。還答應負值。生成人工主鍵還有其它方法。假如一次只有一位用戶訪問表（并且一次只插入一行），則觸發(fā)器很不錯。將您的主鍵列定義為缺省非空值，這樣當在 INSERT 中沒有指定它時，它就得到一個虛設的值（觸發(fā)器將重寫這個虛設的值）： 上一頁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 的函數(shù)。這個函數(shù)將節(jié)點號（用于多分區(qū)數(shù)據(jù)庫）與時間戳記結(jié)合，因此它可以與企業(yè)擴展版本（EEE）一起使用。IDENTITY 和 SEQUENCE 在 DB2 的下一個主要版本出現(xiàn)前還不能與 EEE 一起使用。GENERATE_UNIQUE 有兩個缺點：數(shù)據(jù)類型（CHAR(13) FOR BIT DATA）不是按順序遞增，并且不象數(shù)值數(shù)據(jù)類型那樣易于使用。更簡單的解決方案是標量子查詢表達式：INSERT INTO Foobar (key_col, ...)VALUES (COALESCE((SELECT MAX(key_col) FROM Foobar) +1, 0) ...)獲得一屏數(shù)據(jù)這些方法對于那些在數(shù)據(jù)庫和應用程序投入生產(chǎn)以前有機會進行一些設計工作的模式和應用程序來說是不錯的。但您還記得那兩個以 A（ARIES（航班訂票環(huán)境仿真）和 ACID（原子性、一致性、隔離和持久性））開頭的 4.5 字母單詞嗎？假如您預定了航班，那么您希望他們在您到達機場時記得這回事。這就是持久性：有用的數(shù)據(jù)是持久的。這意味著即使您定義了一個好的主鍵，有些人可能會查詢結(jié)果集的“前二十行，而不管結(jié)果集中有多少行。更糟的情況是有人要求您顯示第 21 行到 40 行。但等一下，您會提出異議，關(guān)系表中的行沒有順序！對于希望在他們的 Netscape 瀏覽器中一次看到二十行的用戶而言，您就好象在說冰島語。DB2 答應您實時地給結(jié)果集排序，并可以從該結(jié)果集的開始或結(jié)尾部分提取任意數(shù)量的行：SELECT NAME FROM ADDRESSORDER BY NAMEFETCH FIRST 10 ROWS ONLYSELECT NAME FROM ADDRESSORDER BY NAME DESCFETCH FIRST 10 ROWS ONLYORDER BY 將強制在內(nèi)存中對整個結(jié)果集進行排序，所以，為了提高 DB2 服務器性能，我們不這么做（盡管只向客戶機發(fā)送 10 行可能會提高網(wǎng)絡性能）。假如您不關(guān)心順序并且只想知道至少有 10 行符合結(jié)果集，則清除 ORDER BY 以省去 DB2 服務器上的排序： 上一頁12345678下一頁 SELECT NAME FROM ADDRESSFETCH FIRST 10 ROWS ONLY于是現(xiàn)在我們已看到您給行編號并且任意選擇了一個子集。假設我們因某些性能上的好處而給行編號，這必將破壞關(guān)系模型。我們幾乎完全妥協(xié)了，并且已經(jīng)犯了關(guān)系七宗罪中的六宗。還有一條關(guān)系誓約您沒有觸犯：讓我們實時地給行編號，犧牲掉性能和關(guān)系純潔性吧。我們?nèi)绾巫C實這樣做的正確性呢？在因特網(wǎng)上譴責它吧。【導讀】描述了多種常用功能的解決方法，而這些功能并不完全符合關(guān)系誓約。向使用瀏覽器的客戶顯示公司數(shù)據(jù)顯然證實了違反對關(guān)系純潔性和性能推崇所作的承諾。您可以用 rownum 或 rank 函數(shù)實時地給結(jié)果集賦予行號。下面我們?yōu)橛脕碛涗浀刂返谋碇械男信判颍⑦x擇第 11 行到第 20 行。結(jié)果集由名稱和實時創(chuàng)建的名為 rn 的列（它給行編號）組成：SELECT * FROM (SELECT NAME, rownumber() OVER(ORDER BY NAME)AS rn FROM ADDRESS)AS tr WHERE rn BETWEEN 11 and 20rank 更為復雜，并且它答應您以排序的順序標識聯(lián)系，對于足球聯(lián)賽非常理想：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清空表 — 無需通知日志記錄程序（截斷表）現(xiàn)在你已經(jīng)得到了很多精巧的方法來處理你的數(shù)據(jù)了，我們再來學習一個小把戲。其他的數(shù)據(jù)庫產(chǎn)品有被稱為“截斷表的功能，即在不進行日志記錄的情況下刪除表中的所有數(shù)據(jù)，而保留表的結(jié)構(gòu)（假如不想保留表結(jié)構(gòu)，我們就使用 DROP TABLE 命令了）。假如想在 DB2 中得到這種功能，可以執(zhí)行帶有 REPLACE 選項的 LOAD 命令，并使用一個 0 字節(jié)的文件作為導入數(shù)據(jù)源，由于 DB2 的 LOAD 操作是不做日志的，所以可以通過這個小騙局來達到我們的目的。 上一頁12345678下一頁 猜測游戲和鏡屋您的表很不錯 — 為什么要從視圖訪問它？這樣做有許多理由：列級別安全性：排除那些您不希望用戶在定義視圖的 SELECT 中看到的列。行級別安全性：除非您定義一個視圖，否則 Windows／UNIX／OS/2 上的 DB2 v7 不答應您限制對表中某些行的訪問（假如您希望限制對答應用戶看到的內(nèi)容的更新，請記得加上 check 選項）：create view london_football asselect * from footballwhere team in ('Arsenal','Aston Villa')with check option設想這一點對于“人力資源應用程序的作用：用戶可以查看薪水在 $nn,nnn 以下的雇員，給他們加薪而加薪后的薪水不會在 $nn,nnn 以上。DROP COLUMN：DB2 不答應您刪除一個列。我可以想到您希望刪除列的三個理由：回收空間：假如您希望這樣做，可以導出您希望保存的數(shù)據(jù)，刪除那個表，用您需要的那些列重新創(chuàng)建表，然后裝入這個表。這是否代價高昂？當然是，但是回收空間需要這樣或者 REORG TABLE。這些本來就是代價高昂的操作。這個列不再是行的邏輯部分：例如，您意識到您的雇員可能有兩個地址，并且停止跟蹤雇員（employee）表中的地址（雇員表和雇員地址（employee_address）表之間現(xiàn)在有 n:m 關(guān)系）。在雇員表上創(chuàng)建一個不包含地址列的視圖。假如您真的要用新奇的方法，可以使用 RENAME TABLE 命令給基表一個新的名稱，然后將原始表名作為該視圖的名稱。您的視圖也可以連接雇員表中的有用列和從雇員地址獲得的地址。現(xiàn)在我們回到了關(guān)系的正道。列變寬了。假如它是 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)我很喜歡這個視圖，所以我實現(xiàn)它假如派生列對您來說還不夠壞，整個派生表怎么樣？使它與基表中的數(shù)據(jù)匹配或不匹配（以及使每個 SELECT 成為潛在的錯讀）的能力又如何？Oracle 稱這些為實現(xiàn)的視圖。DB2 稱它們?yōu)樽詣訁R總表，在非凡情況下稱為復制匯總表。假如經(jīng)常被問到一個問題（SELECT MAX(ORDERS) FROM LEADS），或者經(jīng)常組裝一個聚集（SELECT COUNT(FRANCHISES) FROM STORES WHERE STATE=’TEXAS’），那么或許值得將結(jié)果集存儲在磁盤上，這樣 DB2 就不必天天重新計算它二十次：非凡當幾天前的數(shù)據(jù)足以準確地支持基于查詢的決策時。讓我們從想知道哪個客戶訂購最多的貪婪的銷售經(jīng)理開始。他們在名為 LEADS 的表中跟蹤這一項，推斷出客戶過去所下訂單的數(shù)目可能有助于確定哪些銷售線索最有可能變?yōu)檎鎸嵉匿N售。這個問題天天會被問幾次（假如您預感這正在發(fā)生并且需要驗證它，您可以使用名為 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創(chuàng)建匯總表后，用這條命令填充它：REFRESH TABLE LEADS_MAX用戶不必了解匯總表。DB2 優(yōu)化器會決定何時使用基本表，何時使用匯總表。請注重 REFRESH DEFERRED 子句：您正在告訴 DB2 舊數(shù)據(jù)在匯總表中是可接受的。這在您不需要準確答案或當前答案時是合適的。它適合構(gòu)建一個業(yè)務計劃，但對于要怎樣存儲銀行余額，它就不適合了。請參閱 SQL Reference 中的非凡寄存器 CURRENT REFRESH AGE 以及 Administration Guide 中的“Creating a Summary Table一節(jié)，以了解在答案可以“足夠接近、無需精確時，如何為匯總表中的舊數(shù)據(jù)設置容忍度。 上一頁12345678下一頁 REFRESH DEFERRED 是總結(jié)只讀表上數(shù)據(jù)的理想選擇。多分區(qū)數(shù)據(jù)庫的非凡匯總表稱為復制匯總表。您將在 DB2 EEE 中使用它以在每個分區(qū)都有小型表（或只讀表）的副本。在 EEE 中，您通常將最大的表（稱為事實表）分布到所有的分區(qū)。大量使用的連接鍵（如客戶號碼）應該作為分區(qū)鍵使用。DB2 將數(shù)據(jù)進行散列處理以對它分區(qū)。這意味著較少使用的連接鍵（如國家／地區(qū)或部門）可能會以次優(yōu)化方式分布。當您在多分區(qū)數(shù)據(jù)庫中連接數(shù)據(jù)時，與組合的連接更快（例如，CUSTOMER 和 COUNTRY 表中所有 COUNTRY 為 Argentina 的行都在同一分區(qū)）。假如 COUNTRY 不是分區(qū)鍵，這是不可能的。要獲得組合，您可以將較小的表限制在一個分區(qū)，然后創(chuàng)建一個將它復制到其它分區(qū)的復制匯總表。這一策略在所復制的表較小或很少有更改時奏效（假如您在經(jīng)常更改國名的國家做生意的話，要避免這么做）。假如表確實很小（如各大洲的列表），不要費心去復制它：DB2 將把它傳送到所有分區(qū)并在連接期間將它保留在內(nèi)存中。不要擔心通過名稱連接到副本：判定副本表何時可以提高性能是 DB2 的工作。通過使匯總表 REFRESH IMMEDIATE，可以將它們用于動態(tài)數(shù)據(jù)。這有比 REFRESH DEFERRED 更嚴格的規(guī)則，所以請仔細閱讀 SQL Reference。在首次創(chuàng)建匯總表之后，您仍必須使用 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也可以這樣我們現(xiàn)在已研究了兩種視圖。作為標準視圖，視圖定義存儲在數(shù)據(jù)庫中（在 SYSCAT.VIEWS.TEXT 中）而數(shù)據(jù)只存儲在基表中。我們可以通過創(chuàng)建匯總表使得在這個數(shù)據(jù)上執(zhí)行 SELECT 操作更快，這是以冗余數(shù)據(jù)為代價，它消耗更多磁盤空間并使得 INSERT、UPDATE 和 DELETE 更慢（或讓基表和匯總表不同步，至少在下一次刷新以前是這樣）。還有另一個極端：創(chuàng)建一個僅在數(shù)據(jù)庫連接期間存在的聚集，或者甚至和 SQL 語句的生命期一樣短。第一個稱為 DECLARED TEMPORARY TABLE，第二個稱為 COMMON TABLE EXPRESSION，也稱為 TEMPORARY RESULT TABLE。一個聲明的臨時表需要一個 USER TEMPORARY TABLESPACE，您可以用 CREATE TABLESPACE 命令創(chuàng)建它（請參閱 SQL Reference）。您可以將這個臨時表聲明為應用程序運行時數(shù)據(jù)的保留位置。DECLARE GLOBAL TEMPORARY TABLE table1(column1 INT, column2 INT)NOT LOGGED您用模式 SESSION 限定表，因為它屬于您連接到數(shù)據(jù)庫時創(chuàng)建的會話：INSERT INTO SESSION.TABLE1 VALUES (4,5)SELECT * FROM SESSION.TABLE1您可能希望這個臨時表與現(xiàn)有表匹配，因此您可以用現(xiàn)有表的 SELECT 語句填充它。假如這樣的話，使用 LIKE 創(chuàng)建它：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您現(xiàn)在已被護送出紅燈區(qū)。公共表表達式并不違反關(guān)系原則：它不要求 DB2 存儲派生數(shù)據(jù)，也不添加人工列。假如一定要從這個故事引出一個寓意的話，假定用計算機解決一個問題有 n 種方法。一種方法可能成本最低，一種方法對您而言最快，一種方法對用戶而言最快，而另一種方法對于繼續(xù)您的設計以進行維護和添加新功能的開發(fā)人員而言最快。至于哪種選擇最好，就作為習題留給讀者吧。 上一頁12345678