1、優化Oracle庫表設計的若干方法前言絕大多數的Oracle數據庫性能問題都是由于數據庫設計不合理造成的，只有少部分問題根植于Database Buffer、Share Pool、Redo Log Buffer等內存模塊配置不合理，I/O爭用，CPU爭用等DBA職責范圍上。所以除非是面對一個業已完成不可變更的系統，否則我們不應過多地將關注點投向內存、I/O、CPU等性能調整項目上，而應關注數據庫表本身的設計是否合理，庫表設計的合理性才是程序性能的真正執牛耳者。合理的數據庫設計需要考慮以下的方面：業務數據以何種方式表達。如一個員工有多個Email，你可以在T_EMPLOYEE表中建立多個Emai

2、l字段如email_1、email_2、 email_3，也可以創建一個T_EMAIL子表來存儲，甚至可以用逗號分隔開多個Email地址存放在一個字段中。數據以何種方式物理存儲。如大表的分區，表空間的合理設計等。如何建立合理的數據表索引。表索引幾乎是提高數據表查詢性能最有效的方法，Oracle擁有類型豐富的數據表索引類型，如何取舍選擇顯得特別重要。本文我們將目光主要聚焦于數據表的索引上，同時也將提及其他兩點的內容。通過對一個簡單的庫表設計實例的分析引出設計中的不足，并逐一改正。考慮到手工編寫庫表的SQL腳本原始且低效，我們將用目前最流行的庫表設計工具PowerDesigner 10來講述表設計

3、的過程，所以在本文中你還會了解到一些相關的PowerDesigner的使用技巧。一個簡單的例子某個開發人員著手設計一個訂單的系統，這個系統中有兩個主要的業務表，分別是訂單基本信息表和訂單條目表，這兩張表具有主從關系的表，其中T_ORDER是訂單主表，而T_ORDER_ITEM是訂單條目表。數據庫設計人員的設計成果如圖 1所示：圖 1 訂單主主從表ORDER_IID是訂單號號，為T_OORDER的的主鍵，通過過名為SEQQ_ORDEER_ID的的序列產生鍵鍵值，而ITTEM_IDD是T_ORRDER_IITEM表的的主鍵，通過過名為SEQQ_ORDEER_ITEEM的序列產產生鍵值，TT_ORD

4、EER_ITEEM通過ORRDER_IID外鍵關聯聯到T_ORRDER表。需求文檔指出訂單記錄將通過以下兩種方式來查詢數據：CLIENT + ORDER_DATE+IS_SHPPED：根據客戶訂貨日期+是否發貨條件查詢訂單及訂單條目。ORDER_DATE+IS_SHIPPED：根據訂貨日期+是否發貨條件查詢訂單及訂單條目。數據庫設計人員根據這個要求，在T_ORDER表的CLIENT、 ORDER_DATE及IS_SHPPED三字段上建立了一個復合索引IDX_ORDER_COMPOSITE；在T_ORDER_ITEM為外鍵 ORDER_ID建立IDX_ORDER_ITEM_ORDER_ID索引。

5、讓我們看一下該份設計的最終SQL腳本：/*訂單表*/ creaate taable TT_ORDEER (ORRDER_IID NUMMBER(110) noot nulll, ADDDRESSS VARCCHAR2(100), CLIEENT VAARCHARR2(60), ORDDER_DAATE CHHAR(8), IS_SHIPPPED CHHAR(1), connstraiint PKK_T_ORRDER pprimarry keyy (ORDDER_IDD);creaate inndex IIDX_CLLIENT on T_ORDERR (CLLIENT ASC, ORDEER_D

6、ATTE ASCC, ISS_SHIPPPED AASC);/*訂單條目子子表*/crreate tablee T_ORRDER_IITEM (ITEMM_ID NNUMBERR(10) not nnull,ORDERR_ID NNUMBERR(10), ITEEM VARRCHAR22(20), COUUNT NUUMBER(10), consstrainnt PK_T_ORDDER_ITTEM prrimaryy key (ITEMM_ID);creaate inndex IIDX_ORRDER_IITEM_OORDER_ID onn T_ORRDER_IITEM (ORDEER_ID A

7、SC);alteer tabble T_ORDERR_ITEMM add consttraintt FK_TT_ORDEER_REEFERENNCE_T_ORDERR foreeign kkey (OORDER_ID) rrefereences T_ORDDER (OORDER_ID);我們承認在ERR關系上，這這份設計并不不存在的缺陷陷，但卻存在在以下有待優優化的地方：沒有將表表數據和索引引數據存儲到到不同的表空空間中，而不不加區別地將將它們存儲到到同一表空間間里。這樣，不不但會造成II/O競爭，也也為數據庫的的維護工作帶帶來不便。ORACLLE會自動為為表的主鍵列列創建一個普普通B-Trr

8、ee索引，但但由于這兩張張表的主鍵值值都通過序列列提供，具有有嚴格的順序序性（升序或或降序），此此時手工為其其指定一個反反鍵索引（rreversse keyy indeex）將更加加合理。在在子表T_OORDER_ITEM外外鍵列ORDDER_IDD上建立的IIDX_ORRDER_IITEM_OORDER_ID的普通通B-Treee索引非常常適合設置為為壓縮型索引引，即建立一一個壓縮型的的B-Treee索引。因因為一份訂單單會對應多個個訂單條目，這這就意味著TT_ORDEER_ITEEM表存在許許多同值的 ORDERR_ID列值值，通過將其其索引指定為為壓縮型的BB-Treee索引，不但但可以

9、減少IIDX_ORRDER_IITEM_OORDER_ID所需的的存儲空間，還還將提高表操操作的性能。企圖僅通過建立一個包含3字段IDX_ORDER_COMPOSITE復合索引滿足如前所述的兩種查詢條件方式的索引是有問題的，事實上使用ORDER_DATE+IS_SHIPPED復合條件的查詢將利用不到IDX_ORDER_COMPOSITE索引。 優化設計1、將將表數據和索索引數據分開開表空間存儲儲1.1 表表數據和索引引為何需要使使用獨立的表表空間Oraacle強烈烈建立，任何何一個應用程程序的庫表至至少需要創建建兩個表空間間，其中之一一用于存儲表表數據，而另另一個用于存存儲表索引數數據。因為如

10、如果將表數據據和索引數據據放在一起，表表數據的I/O操作和索索引的I/OO操作將產生生影響系統性性能的I/OO競爭，降低低系統的響應應效率。將表表數據和索引引數據存放在在不同的表空空間中（如一一個為APPP_DATAA，另一個為為APP_IIDX），并并在物理層面面將這兩個表表空間的數據據文件放在不不同的物理磁磁盤上，就可可以避免這種種競爭了。擁擁有獨立的表表空間，就意意味著可以獨獨立地為表數數據和索引數數據提供獨立立的物理存儲儲參數，而不不會發生相互互影響，畢竟竟表數據和索索引數據擁有有不同的特性性，而這些特特性又直接影影響了物理存存儲參數的設設定。此外，表表數據和索引引數據獨立存存儲，還會

11、帶帶來數據管理理和維護上的的方面。如你你在遷移一個個業務數據庫庫時，為了降降低數據大小小，可以只遷遷出表數據的的表空間，在在目標數據庫庫中通過重建建索引的方式式就可以生成成索引數據了了。1.2 表數據和索索引使用不同同表空間的SSQL語法指指定表數據及及索引數據存存儲表空間語語句最簡單的的形式如下。將表數據存儲在APP_DATA表空間里：create table T_ORDER ( ORDER_ID NUMBER(10) not null, )tablespace APP_DATA;將索引數據存儲在APP_IDX表空間里：create index IDX_ORDER_ITEM_ORDER_ID

12、 on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;1.3 PowerDesigner中如何操作1) 首先，必須創建兩個表空間。通過Model-Tablespace.在List of Tablespaces中創建兩個表空間：圖 2 創建表表空間2) 為每張表表指定表數據據存儲的表空空間。在設計計區中雙擊表表，打開Taable PProperrties設設計窗口，切切換到opttions 頁，按圖 3所示指定定表數據的存存儲表空間。圖 3 指定表表數據的存儲儲表空間3) 為每個索索引指定索引引數據的存儲儲表空間。在在Tablee Proppert

13、iees中切換到到Indexxes頁，在在這里列出了了表的所有索索引，雙擊需需設置表空間間的索引，在在彈出的Inndex PProperrties窗窗口中切換到到Optioons頁，按按如下方式指指定索引的存存儲表空間。圖 4 指定索索引數據的存存儲表空間將表空間的問題題延展一下：一個應用系系統庫表的表表空間可以進進行更精細的的劃分。首先先，如果表中中存在LOBB類型的字段段，有為其指指定一個特定定的表空間，因因為LOB類類型的數據在在物理存儲結結構的管理上上和一般數據據的策略有很很大的不同，將將其放在一個個獨立的表空空間中，就可可方便地設置置其物理存儲儲參數了。其其次，需要考考慮庫表數據據的

14、DML操操作特性：根根據DML（IINSERTT,UPDAATE,DEELETE）操操作頻繁程度度，將幾乎不不發生任何DDML操作的的數據放在獨獨立的表空間間中，因為極極少DML操操作的表可設設置符合其特特性的物理參參數：如PCCTFREEE可置為0，其其BUFFEER_POOOL指定為KKEEP，以以便將數據緩緩存在KEEEP數據緩存存區中等等，不不一而足。此此外，還可以以考慮按業務務需要將不同同的業務模塊塊分開存放，這這主要是考慮慮到備份問題題。假設我們們有一部分業業務數據重要要性很強，而而其他的業務務數據重要性性相對較弱，這這樣就可以將將兩者分開存存儲，以便設設置不同的備備份策略。 當然

15、，無節節制的細化表表空間也將帶帶來管理上和和部署上的復復雜，根據業業務需求合理理地規劃表空空間以達到管管理和性能上上的最佳往往往需要更多的的權衡。2、顯式為主主鍵列建立反反向鍵索引22.1 反向向鍵索引的原原理和用途我我們知道Orracle會會自動為表的的主鍵列建立立索引，這個個默認的索引引是普通的BB-Treee索引。對于于主鍵值是按按順序(遞增增或遞減)加加入的情況，默默認的B- Tree索索引并不理想想。這是因為為如果索引列列的值具有嚴嚴格順序時，隨隨著數據行的的插入，索引引樹的層級增增長很快。搜搜索索引發生生的I/O讀讀寫次數和索索引樹的層級級數成正比，也也就是說，一一棵具有5個個層級

16、的B-Tree索索引，在最終終讀取到索引引數據時最多多可能發生多多達5次I/O操作。因因而，減少索索引的層級數數是索引性能能調整的一個個重要方法。如果索引列的數據以嚴格的有序的方式插入，那么B-Tree索引樹將變成一棵不對稱的歪樹，如圖 5所示：圖 5不對稱的的B-Treee索引而如果索引列的的數據以隨機機值的方式插插入，我們將將得到一棵趨趨向對稱的索索引樹，如圖圖 6所示：圖 6對稱的BB-Treee索引比較圖 5和圖圖 6，在圖圖 5中搜索索到A塊需要要進行5次II/O操作，而而圖 6僅需需要3次I/O操作。既既然索引列數數據從序列中中獲取，其有有序性無法規規避，但在建建立索引時，OOra

17、clee允許對索引引列的值進行行反向，即預預先對列值進進行比特位的的反向，如 1000,100011,100111,101111,11100經過反反向后的值將將是00011,10011,11011,00111。顯然經過過位反向處理理的有序數據據變得比較隨隨機了，這樣樣所得到的索索引樹就比較較對稱，從而而提高表的查查詢性能。但但反向鍵索引引也有它局限限性：如果在在WHEREE語句中，需需要對索引列列的值進行范范圍性的搜索索，如BETTWEEN、等，其其反向鍵索引引無法使用，此此時，Oraacle將執執行全表掃描描；只有對反反向鍵索引列列進行 和 = 的比較操作作時，其反向向鍵索引才會會得到使用。

18、2.2 反向鍵索引的SQL語句回到我們上面的例子，由于T_ORDER和T_ORDER_ITEM的主鍵值來源于序列，主鍵值是有嚴格順序的，所以我們應該摒棄默認的Oracle所提供的索引，而采取顯式為主鍵指定一個反向鍵索引的方式。ORDER_ID為T_ORDER表的主鍵，主鍵名為PK_ORDER，我們為ORDER_ID列上建立一個反向鍵索引IDX_ORDER_ID，并使PK_ORDER_ID使用這個索引，其SQL語句如下：create tablee T_ORRDER ( ORDDER_IDD NUMBBER(100) nott nulll,CLIIENT VVARCHAAR2(600), AADD

19、RESSS VARRCHAR22(100),ORDDER_DAATE CHHAR(8);creeate uuniquee indeex IDXX_ORDEER_ID on T_ORDERR ( ORRDER_IID ASCC) revverse;alterr tablle T_OORDER add cconstrraint PK_ORRDER pprimarry keyy (ORDDER_IDD) usiing inndex IIDX_ORRDER_IID;要保證創建IDDX_ORDDER_IDD的SQL語語句在創建PPK_ORDDER主鍵的的SQL語句句之前，因為為主鍵需要引引用到這個反反向

20、鍵索引。由于主鍵列的數據是唯一的，所以為IDX_ORDER_ID加上unique限定，使其成為唯一型的索引。2.3 PowerdDesigner如何操作1) 首先，需要為ORDER_ID列建立一個反向鍵索引。打開T_ORDER的Table Properties的窗口，切換到Indexes頁，新建一個名為IDX_ORDER_ID的索引。填寫完索引的名稱后，雙擊這個索引，彈出Index Properties窗口，在這個窗口的Columns中選擇ORDER_ID列。然后，切換到Options頁，按圖 7的方式將其設置為反向鍵索引。圖 8 為主鍵鍵指定特定的的索引不可否認PowwerDessigner

21、r確實是目前前業界最強大大易用的數據據庫設計工具具，但很遺憾憾，當我們為為表主鍵指定定一個索引時時，其產生的的語句在順序序上有問題：即創建主鍵鍵的語句位于于創建索引語語句之前：create tablee T_ORRDER ();allter ttable T_ORDDER addd connstraiint PKK_T_ORRDER pprimarry keyy (ORDDER_IDD) usiing inndex IIDX_ORRDER_IID;creeate uuniquee indeex IDXX_ORDEER_ID on T_ORDERR ( ORRDER_IID ASCC) revv

22、erse;我們可以通過對對PowerrDesiggner生成成SQL語句句的設置進行行調整，先生生成創建表和和索引的SQQL語句，再再創建為表添添加主鍵和外外鍵的SQLL語句來達到到曲線救國的的目的，請看看下一步。33)通過菜單單Databbase-Generrate DDatabaase.調出Dattabasee Conffiguraation窗窗口，切換到到Keys&Indexxes頁，按按圖 9設置置：圖 9 設置生生成鍵和索引引SQL的選選項這里，我們將PPrimarry Keyys和Forreign keys的的選項都取消消，而將Inndexess勾選，以達達到只生成表表的索引SQQ

23、L語句的目目的。點擊確定后，生生成創建數據據庫表及其索索引的SQLL語句，運行行該SQL創創建數據庫后后，再按圖 10設置生生成為表添加加主鍵和外鍵鍵的SQL語語句：圖 10 生成成創建表主鍵鍵和外鍵的SSQL語句除此設置外，還還必須切換到到Tablees & VViews頁頁下，取消所所有選項，避避免重新生成成創建表的語語句。 3、將子表的的外鍵列的索索引改為壓縮縮型3.1 壓縮型索引引的原理和用用途在前面的的例子中，由由于一條訂單單會對應多條條訂單條目，所所以T_ORRDER_IITEM的OORDER_ID字段總總會出現重復復的值，如：ITEM_IDD ORDEER_ID ITEM COU

24、NTT1 1000 101 12 1000 1044 23 1100 2001 34 200 3301 255 200 401 116 2000 205 3在ORDER_ID列上創創建一個普通通未壓縮的BB-Treee索引，則索索引數據的物物理上的存儲儲形式如下：圖 11 未進進行壓縮的索索引存儲ORDER_IID的重復值值在索引塊中中重復出現，這這樣不但增加加了存儲空間間的需求，而而且因為查詢詢時需要讀取取更多的索引引數據塊，所所以查詢性能能也會降低。讓我們來來看一下經過過壓縮后索引引數據的存儲儲方式：圖 12 進行行壓縮的索引引存儲壓縮型的索引消消除了重復的的索引值，將將相同索引列列值所關

25、聯的的ROWIDD存儲在一起起。這樣，不不但節省了存存儲空間，查查詢效率也提提高了，真可可謂兩全齊美美了。對象TT_ORDEER和T_OORDER_ITEM這這樣的主從表表進行查詢時時，一般情況況下，我們都都必須通過外外鍵查詢出子子表所有關聯聯的記錄，所所以在子表的的外鍵上建立立壓縮型的索索引是非常適適合的。3.2 壓縮型型索引的SQQL語句創建建壓縮型索引引的SQL語語句非常簡單單，在T_OORDER_ITEM的的ORDERR_ID上創創建壓縮型索索引的SQLL如下所示：create indexx IDX_ORDERR_ITEMM_ORDEER_ID on T_ORDERR_ITEMM (

26、ORRDER_IID ASCC) commpresss;需要在創建索引引的語句后附附上comppress關關鍵字就可以以了。3.33 PoweerDesiigner如如何創建壓縮縮型索引1) 打開T_ORDERR_ITEMM表的Tabble Prropertties的窗窗口，切換到到Indexxes頁，為為ORDERR_ID列創創建一個名為為IDX_OORDER_ITEM_ORDERR_ID的索索引。2) 雙擊IDXX_ORDEER_ITEEM_ORDDER_IDD彈出Inddex Prropertties窗口口，切換到OOptionns頁，按圖圖 13將索索引設置為壓壓縮型：圖 13 將索索

27、引指定為壓壓縮型4、建立滿足需需求的復合鍵鍵索引 設計計人員希望通通過T_ORRDER表上上的IDX_ORDERR_COMPPOSITEE復合索引滿滿足以下兩種種組合條件的的查詢：CCLIENTT + ORRDER_DDATE + IS_SSHIPPEEDORDDER_DAATE + IS_SHHIPPEDD為方便闡述述，我們特地地將IDX_ORDERR_COMPPOSITEE的創建SQQL語句再次次列出：create indexx IDX_ORDERR_COMPPOSITEE on TT_ORDEER ( CCLIENTT ASC, ORDEER_DATTE ASCC, IS_SHIPPPE

28、D ASSC);事實上，在CLLIENT + ORDDER_DAATE + IS_SHHIPPEDD 三列上所所執行的復合合條件查詢會會應用到這個個索引，而在在ORDERR_DATEE + ISS_SHIPPPED列上上所執行的復復合查詢不會會使用這個索索引，因而將將導致一個全全表掃描的操操作。可以用用許多工具來來了解查詢語語句的執行計計劃，通過SSET AUUTOTRAACE ONN來查詢以上上兩個復合查查詢的執行計計劃：打開SSQL/Pllus，輸入入以下的語句句：SQL seet auttotracce onSSQL sselectt * frrom t_orderr wherre CL

29、IIENT = 1 and OORDER_DATE=1 aand ISS_SHIPPPED=1;分析得到的執行行計劃為：SELECT STATEEMENT Optimmizer=CHOOSSETABLLE ACCCESS (BY INNDEX RROWID) OF T_ORDDER IINDEX (RANGGE SCAAN) OFF IDXX_ORDEER_COMMPOSITTE (NNON-UNNIQUE)可見Oraclle先利用IIDX_ORRDER_CCOMPOSSITE得到到滿足條件的的記錄ROWWID，再通通過ROWIID返回記錄錄。而下面查查詢語句：SQL seelect * fr

30、oom t_oorder wheree ORDEER_DATTE=1 and IS_SHHIPPEDD=1的執行計劃則為為：SELECT STATEEMENT Optimmizer=CHOOSSE TABBLE ACCCESS (FULLL) OF T_ORRDER很明顯，Oraacle在TT_ORDEER表上執行行了一個全表表掃描的操作作，沒有用到到IDX_OORDER_COMPOOSITE索索引。對復合合列索引，我我們得出這個個結論：假設設在COL_1,COLL_2,COL_nn這些列上建建立了一個復復合索引：create indexx IDX _COMPPOSITEE on TTABLE

31、11COL_11,COL_2,COL_n則只有WHERRE語句上包包含COL_1(復合索索引的第一個個字段)的查查詢才會使用用這個復合索索引，而未包包含COL_1的查詢則則不會使用這這個復合索引引。回到我們們的例子，如如何建立滿足足CLIENNT + OORDER_DATE + IS_SHIPPPED和ORRDER_DDATE + IS_SSHIPPEED兩種查詢詢的索引呢？考慮到ISS_SHIPPPED列基基數很小，只只有兩個可能能的值：0,1。在這種種情況下，有有兩種方案：第一，分別別為CLIEENT + ORDERR_DATEE + ISS_SHIPPPED和OORDER_DATE +

32、IS_SHIPPPED建立一一個復合索引引；第二，分分別在CLIIENT和OORDER_DATE列列上建立一個個索引，而IIS_SHIIPEED列列不建立索引引。第一種方方案的查詢效效率最快，但但因為CLIIENT和OORDER_DATE在在索引中會重重復出現兩次次，占用較大大的存儲空間間。第二種方方案CLIEENT和ORRDER_DDATE不會會在索引存儲儲出現兩次，較較為節省空間間，查詢效率率比之于第一一種方案會稍稍低一些，但但影響不大。 我們采用第第二種方案為為CLIENNT和ORDDER_DAATE分別創創建索引IDDX_CLIIENT和IIDX_ORRDER_DDATE，組組合查詢條件件為CLIEENT + ORDERR_DATEE + ISS_SHIPPPED時的的執行計劃為為：SELECT STATEEMENT Optimmizer=CHOOSSE TABBLE ACCCESS (BY IINDEX ROWIDD) OF T_ORRDER AND-EEQUAL INDEXX (RANNGE SCCAN) OOF IDDX_CLIIENT (NON-UNIQUUE) INNDEX (RANGEE SCANN) OF IDX_ORDERR_DATEE (NOON-UNIIQUE)而組合條件為OORDER_DATE + IS_