Oracle 分區(qū)表的優(yōu)點(diǎn)

1、ORACLE表分區(qū)表分區(qū)的好處和事處理表分區(qū)描述表分區(qū)(partition):表分區(qū)技術(shù)是在超大型數(shù)據(jù)庫(kù)(VLDB)中將大表及其索引通過分區(qū)(patition)的形式分割為若干較小、可管理的小塊，并且每一分區(qū)可進(jìn)一步劃分為更小的子分區(qū)(subpartition)o而這種分區(qū)對(duì)于應(yīng)用來說是透明的。Oracle的表分區(qū)功能通過改善可管理性、性能和可用性，從而為各式應(yīng)用程序帶來了極大的好處。通常，分區(qū)可以使某些查詢以及維護(hù)操作的性能大大提高。此外,分區(qū)還可以極大簡(jiǎn)化常見的管理任務(wù)，分區(qū)是構(gòu)建千兆字節(jié)數(shù)據(jù)系統(tǒng)或超高可用性系統(tǒng)的關(guān)鍵工具。分區(qū)功能能夠?qū)⒈?、索引或索引組織表進(jìn)一步細(xì)分為段，這些數(shù)據(jù)庫(kù)對(duì)象的

2、段叫做分區(qū)。每個(gè)分區(qū)有自己的名稱，還可以選擇自己的存儲(chǔ)特性。每個(gè)分區(qū)都是一個(gè)獨(dú)立的段(SEGMENT)，可以存放到相同(不同)的表空間中。從數(shù)據(jù)庫(kù)管理員的角度來看，一個(gè)分區(qū)后的對(duì)象具有多個(gè)段，這些段既可進(jìn)行集體管理，也可單獨(dú)管理，這就使數(shù)據(jù)庫(kù)管理員在管理分區(qū)后的對(duì)象時(shí)有相當(dāng)大的靈活性。但是，從應(yīng)用程序的角度來看，分區(qū)后的表與非分區(qū)表完全相同，使用SQLDML命令訪問分區(qū)后的表時(shí)，無需任何修改。(對(duì)于高效率查詢是有影響,主要差別是對(duì)某一分區(qū)數(shù)據(jù)時(shí)行查詢時(shí)和對(duì)整體數(shù)據(jù)進(jìn)行查詢)表分區(qū)的好處通過對(duì)表進(jìn)行分區(qū)，可以獲得以下的好處：增強(qiáng)可用性：如果表的某個(gè)分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用；維護(hù)方

3、便：如果表的某個(gè)分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可；均衡I/O：可以把不同的分區(qū)映射到磁盤以平衡I/O,改善整個(gè)系統(tǒng)性能；改善查詢性能：對(duì)分區(qū)對(duì)象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度。分區(qū)表事務(wù)操作的時(shí)候，指定了分區(qū)，系統(tǒng)在執(zhí)行的時(shí)候則只操作該分區(qū)的記錄，提高了數(shù)據(jù)處理的速度。不要指定分區(qū)直接操作數(shù)據(jù)也是可以的。在分區(qū)表上建索引及多索引的使用和非分區(qū)表一樣。此外，因?yàn)樵诰S護(hù)分區(qū)的時(shí)候可能對(duì)分區(qū)的索引會(huì)產(chǎn)生一定的影響，可能需要在維護(hù)之后重建索引，相關(guān)內(nèi)容請(qǐng)參考分區(qū)表索引部分的文檔缺點(diǎn)分區(qū)表相關(guān)：已經(jīng)存在的表沒有方法可以直接轉(zhuǎn)化為分區(qū)表。不過Oracle提供了在線重定義表的功能。

4、什么時(shí)候使用分區(qū)表1、表的大小超過2GB。2、表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加都新的分區(qū)中。各版本表分區(qū)的特性區(qū)別面的表列出了每個(gè)版本中包括的分區(qū)特性：Oracle數(shù)據(jù)庫(kù)版本分區(qū)特性8.0.5引入范圍分區(qū)8i引入哈希和混合范圍-哈希分區(qū)9i引入列表分區(qū)，混合范圍-列表分區(qū)10g引入范圍，列表和索引組織表的哈希分區(qū)，還引入了其他混合分區(qū)類型(range-hash,range-list)11g引用分區(qū)、間隔分區(qū)、虛擬列分區(qū)以及擴(kuò)展的組合分區(qū)引入分區(qū)擴(kuò)展：間隔分區(qū)REF分區(qū)基于列的虛擬分區(qū)分區(qū)顧問(range-range,list-range,list-list,list-hash)分區(qū)別的類型/范

5、圍分區(qū)：將表按某一字段或若干個(gè)字段的取值范圍分區(qū)。/hash分區(qū)：將表按某一字段的值均勻地分布到若干個(gè)指定的分區(qū)。/復(fù)合分區(qū)：結(jié)合了前面兩種分區(qū)類型的優(yōu)點(diǎn)，首先通過值范圍將表進(jìn)行分區(qū)，然后以hash模式將數(shù)據(jù)進(jìn)一步均勻分配至物理存儲(chǔ)位置。/列表分區(qū)：數(shù)據(jù)分布是通過分區(qū)鍵的一串值定義的，這對(duì)不連續(xù)的列表非常有用，如：區(qū)域、狀態(tài)等。(9I以上支持)/混合分區(qū)：有兩個(gè)數(shù)據(jù)分布辦法用于創(chuàng)建混合分區(qū)，表首先通過第一個(gè)數(shù)據(jù)分布辦法進(jìn)行初始化分區(qū)，然后每個(gè)分區(qū)再通過第二個(gè)辦法分成子分區(qū)，下面列出了可用的混合分區(qū)類型：范圍-哈希，范圍-列表，范圍-范圍，列表-范圍，列表-列表，列表-哈希。在10g中索引組織表

6、(表的索引和數(shù)據(jù)存儲(chǔ)在一起)支持通過范圍、列表或哈希進(jìn)行分區(qū)，然而，混合分區(qū)在索引組織表上不受支持。/間隔分區(qū)：在11g中才引入，間隔分區(qū)是對(duì)范圍分區(qū)的擴(kuò)展，為等距范圍分區(qū)提供了自動(dòng)化，分區(qū)創(chuàng)建為元數(shù)據(jù)，只有分區(qū)開始部分是不變的，附加的段是當(dāng)數(shù)據(jù)抵達(dá)時(shí)才分配的，附加分區(qū)和本地索引是自動(dòng)創(chuàng)建的。SQLCREATETABLESALES_PART(TIMEDNUMBER,REGION_IDNUMBER,ORDER_IDNUMBER,ORDER_DATEDATE,SALES_QTYNUMBER(10,2),SALES_AMOUNTNUMBER(12,2)PARTITIONBYRANGE(ORDER_D

7、ATE)INTERVAL(NUMTOYMINTERVAL(1,month)(PARTITIONp_firstVALUESLESSTHAN(01-JAN-2006);numtoyminterval函數(shù)轉(zhuǎn)換一個(gè)數(shù)字為間隔一年至一個(gè)月的文字(年或月)，間隔分區(qū)表可以有傳統(tǒng)的范圍和自動(dòng)間隔部分，范圍分區(qū)表可以通過在ALTERTABLE命令中使用SETINTERVAL選項(xiàng)被擴(kuò)展為間隔分區(qū)表。/REF分區(qū)：這個(gè)分區(qū)方案假設(shè)關(guān)聯(lián)表能從相同的分區(qū)策略中受益，子表通過PK-FK(主鍵-外鍵)關(guān)系繼承主表的策略，它不需要分區(qū)鍵存儲(chǔ)在子表中,通過PARTITIONBYREFERENCE關(guān)鍵字指定，子表繼承主表的分區(qū)

8、策略。/基于列的虛擬分區(qū)：在Oracle以前的版本中，只有分區(qū)鍵物理存在于表中才能對(duì)表進(jìn)行分區(qū)，在11g中引入一個(gè)新的特性“虛擬列”移除了這個(gè)限制，允許分區(qū)鍵通過使用一個(gè)或多個(gè)表的列的表達(dá)式進(jìn)行定義，虛擬列僅作為元數(shù)據(jù)存儲(chǔ)。如：向表ACCOUNTS添加一個(gè)虛擬列：SQLCREATETABLEACCOUNTS(acc_nonumber(10)notnull,acc_namevarchar2(50)notnull,acc_locvarchar2(5),acc_branchnumber(2)generatedalwaysas(to_number(substr(to_char(acc_no),1,2)

9、;使用虛擬列作為分區(qū)鍵：SQLCREATETABLEaccounts(acc_nonumber(10)notnull,acc_namevarchar2(50)notnull,acc_locvarchar2(5),acc_branchnumber(2)generatedalwaysas(to_number(substr(to_char(acc_no),1,2)partitionbylist(acc_branch);/分區(qū)顧問Oracle11g也提供了分區(qū)顧問，它支持生成分區(qū)建議，與10g中為物理視圖、物理視圖日志和索引提供建議類似，實(shí)際上，分區(qū)顧問是Oracle11g中SQL訪問顧問的一部分，這

10、個(gè)顧問幫助生成建議，它將預(yù)先收集實(shí)施分區(qū)后的性能，它還生成創(chuàng)建高效分區(qū)的腳本，可以手動(dòng)通過SQL*plus或通過企業(yè)管理器隊(duì)列提交給Oracle。分區(qū)表的創(chuàng)建范圍分區(qū)將數(shù)據(jù)基于范圍映射到每一個(gè)分區(qū)，這個(gè)范圍是你在創(chuàng)建分區(qū)時(shí)指定的分區(qū)鍵決定的。這種分區(qū)方式是最為常用的，并且分區(qū)鍵經(jīng)常采用日期。當(dāng)使用范圍分區(qū)時(shí)，請(qǐng)考慮以下幾個(gè)規(guī)則：1、每一個(gè)分區(qū)都必須有一個(gè)VALUESLESSTHEN子句，它指定了一個(gè)不包括在該分區(qū)中的上限值。分區(qū)鍵的任何值等于或者大于這個(gè)上限值的記錄都會(huì)被加入到下一個(gè)高一些的分區(qū)中。2、所有分區(qū)，除了第一個(gè)，都會(huì)有一個(gè)隱式的下限值，這個(gè)值就是此分區(qū)的前一個(gè)分區(qū)的上限值。3、在最

11、高的分區(qū)中，MAXVALUE被定義。MAXVALUE代表了一個(gè)不確定的值。這個(gè)值高于其它分區(qū)中的任何分區(qū)鍵的值，也可以理解為高于任何分區(qū)中指定的VALUELESSTHEN的值，同時(shí)包括空值。范圍分區(qū)就是對(duì)數(shù)據(jù)表中的某個(gè)值的范圍進(jìn)行分區(qū)，根據(jù)某個(gè)值的范圍，決定將該數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)上。如根據(jù)序號(hào)分區(qū)，根據(jù)業(yè)務(wù)記錄的創(chuàng)建日期進(jìn)行分區(qū)等。根據(jù)序號(hào)分區(qū)建表：SQLcreatetabledinya_test(transaction_idnumberprimarykey,item_idnumber(8)notnull,item_descriptionvarchar2(300),transaction_da

12、tedatenotnullTOC o 1-5 h z)partitionbyrange(transaction_id)(partitionpart_01valueslessthan(30000000)tablespacedinya_space01,partitionpart_02valueslessthan(60000000)tablespacedinya_space02,partitionpart_03valueslessthan(maxvalue)tablespacedinya_space03);Tablecreated.根據(jù)交易的序號(hào)，交易ID在三千萬以下的記錄將存儲(chǔ)在第一個(gè)表空間din

13、ya_space01中，分區(qū)名為:par_01,在三千萬到六千萬之間的記錄存儲(chǔ)在第二個(gè)表空間：dinya_space02中，分區(qū)名為：par_02，而交易ID在六千萬以上的記錄存儲(chǔ)在第三個(gè)表空間dinya_space03中，分區(qū)名為par_03.根據(jù)交易日期分區(qū)建表：SQLcreatetabledinya_test(transaction_idnumberprimarykey,item_idnumber(8)notnull,item_descriptionvarchar2(300),transaction_datedatenotnull)partitionbyrange(transaction

14、_date)(partitionpart_01valueslessthan(to_date(2006-01-01,yyyy-mm-dd)tablespacedinya_space01,partitionpart_02valueslessthan(to_date(2010-01-01,yyyy-mm-dd)tablespacedinya_space02,partitionpart_03valueslessthan(maxvalue)tablespacedinya_space03);Tablecreated.這樣我們就分別建了以交易序號(hào)和交易日期來分區(qū)的分區(qū)表。每次插入數(shù)據(jù)的時(shí)候，系統(tǒng)將根據(jù)指定的

15、字段的值來自動(dòng)將記錄存儲(chǔ)到制定的分區(qū)(表空間)中。當(dāng)然，我們還可以根據(jù)需求，使用兩個(gè)字段的范圍分布來分區(qū)，如partitionbyrange(transaction_id,transaction_date),分區(qū)條件中的值也做相應(yīng)的改變。Hash分區(qū)(散列分區(qū))是在列值上使用散列算法，以確定將行放入哪個(gè)分區(qū)中。當(dāng)列的值沒有合適的條件時(shí)，建議使用散列分區(qū)。散列分區(qū)為通過指定分區(qū)編號(hào)來均勻分布數(shù)據(jù)的一種分區(qū)類型，因?yàn)橥ㄟ^在I/O設(shè)備上進(jìn)行散列分區(qū)，使得這些分區(qū)大小一致。散列分區(qū)為通過指定分區(qū)編號(hào)來均勻分布數(shù)據(jù)的一種分區(qū)類型，因?yàn)橥ㄟ^在I/O設(shè)備上進(jìn)行散列分區(qū)，使得這些分區(qū)大小一致。如將物料交易表的

16、數(shù)據(jù)根據(jù)交易ID散列地存放在指定的三個(gè)表空間中：SQLcreatetabledinya_test(transaction_idnumberprimarykey,item_idnumber(8)notnull,item_descriptionvarchar2(300),transaction_datedateTOC o 1-5 h z)partitionbyhash(transaction_id)(partitionpart_01tablespacedinya_space01,partitionpart_02tablespacedinya_space02,partitionpart_03tabl

17、espacedinya_space03);Tablecreated.hash分區(qū)最主要的機(jī)制是根據(jù)hash算法來計(jì)算具體某條紀(jì)錄應(yīng)該插入到哪個(gè)分區(qū)中,hash算法中最重要的是hash函數(shù)，Oracle中如果你要使用hash分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用2的n次方，這樣可以使得各個(gè)分區(qū)間數(shù)據(jù)分布更加均勻.復(fù)合分區(qū)有時(shí)候我們需要根據(jù)范圍分區(qū)后，每個(gè)分區(qū)內(nèi)的數(shù)據(jù)再散列地分布在幾個(gè)表空間中，這樣我們就要使用復(fù)合分區(qū)。復(fù)合分區(qū)是先使用范圍分區(qū)，然后在每個(gè)分區(qū)內(nèi)再使用散列分區(qū)的一種分區(qū)方法，如將物料交易的記錄按時(shí)間分區(qū)，然后每個(gè)分區(qū)中的數(shù)據(jù)分三個(gè)子分區(qū),將數(shù)據(jù)散列地存儲(chǔ)在三個(gè)指定的表空間

18、中：SQLcreatetabledinya_test(transaction_idnumberprimarykey,item_idnumber(8)notnull,item_descriptionvarchar2(300),transaction_datedateTOC o 1-5 h z)partitionbyrange(transaction_date)subpartitionbyhash(transaction_id)subpartitions3storein(dinya_space01,dinya_space02,dinya_space03)(partitionpart_01value

19、slessthan(to_date(2006-01-01,yyyy-mm-dd),partitionpart_02valueslessthan(to_date(2010-01-01,yyyy-mm-dd),partitionpart_03valueslessthan(maxvalue);Tablecreated.該例中，先是根據(jù)交易日期進(jìn)行范圍分區(qū)，然后根據(jù)交易的ID將記錄散列地存儲(chǔ)在三個(gè)表空間中。列表分區(qū)（91以上）該分區(qū)的特點(diǎn)是某列的值只有幾個(gè)，基于這樣的特點(diǎn)我們可以采用列表分區(qū)。CREATETABLEPROBLEM_TICKETS(PROBLEM_IDNUMBER(7)NOTNULLPR

20、IMARYKEY,DESCRIPTIONVARCHAR2(2000),CUSTOMER_IDNUMBER(7)NOTNULL,DATE_ENTEREDDATENOTNULL,STATUSVARCHAR2(20)PARTITIONBYLIST(STATUS)(PARTITIONPROB_ACTIVEVALUES(ACTIVE)TABLESPACEPROB_TS01,PARTITIONPROB_INACTIVEVALUES(INACTIVE)TABLESPACEPROB_TS02)組合范圍散列分區(qū)這種分區(qū)是基于范圍分區(qū)和列表分區(qū)，表首先按某列進(jìn)行范圍分區(qū)，然后再按某列進(jìn)行列表分區(qū)，分區(qū)之中的分區(qū)被

21、稱為子分區(qū)。CREATETABLESALES(PRODUCT_IDVARCHAR2(5),SALES_DATEDATE,SALES_COSTNUMBER(10),STATUSVARCHAR2(20)PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYLIST(STATUS)(PARTITIONP1VALUESLESSTHAN(TO_DATE(2003-01-01,YYYY-MM-DD)TABLESPACErptfact2009(SUBPARTITIONP1SUB1VALUES(ACTIVE)TABLESPACErptfact2009,SUBPARTITIONP1

22、SUB2VALUES(INACTIVE)TABLESPACErptfact2009),PARTITIONP2VALUESLESSTHAN(TO_DATE(2003-03-01,YYYY-MM-DD)TABLESPACErptfact2009(SUBPARTITIONP2SUB1VALUES(ACTIVE)TABLESPACErptfact2009,SUBPARTITIONP2SUB2VALUES(INACTIVE)TABLESPACErptfact2009)復(fù)合范圍散列分區(qū)：這種分區(qū)是基于范圍分區(qū)和散列分區(qū)，表首先按某列進(jìn)行范圍分區(qū)，然后再按某列進(jìn)行散列分區(qū)。createtabledinya_

23、test(transaction_idnumberprimarykey,item_idnumber(8)notnull,item_descriptionvarchar2(300),transaction_datedate)partitionbyrange(transaction_date)subpartitionbyhash(transaction_id)subpartitions3storein(dinya_space01,dinya_space02,dinya_space03)(partitionpart_01valueslessthan(to_date(2006-01-01,yyyy-m

24、m-dd),partitionpart_02valueslessthan(to_date(2010-01-01,yyyy-mm-dd),partitionpart_03valueslessthan(maxvalue);擴(kuò)展的組合分區(qū)使用組合分區(qū)Oracle8i數(shù)據(jù)庫(kù)中引入的方案您可以在分區(qū)中創(chuàng)建子分區(qū)，從而進(jìn)一步細(xì)分表。但在該情況下，您只能通過散列子分區(qū)對(duì)已按范圍分區(qū)的表進(jìn)行再次分區(qū)。Oracle9i中對(duì)組合分區(qū)進(jìn)行了擴(kuò)展，使之包括范圍-列表子分區(qū)。這些方案滿足了大多數(shù)環(huán)境(但并非所有環(huán)境)的需要。例如，假設(shè)您有一個(gè)名為SALES的包含多列的表，其中包括兩個(gè)特殊的列，它們是分區(qū)的候選列：sta

25、te_code,它存儲(chǔ)一個(gè)表示銷售狀態(tài)的兩位代碼，表面上用于計(jì)算銷售稅；以及product_code,個(gè)用于識(shí)別銷售記錄所銷售的產(chǎn)品的三位數(shù)字。用戶通過對(duì)兩列進(jìn)行同等的篩選對(duì)該表進(jìn)行查詢，存檔要求也基于這兩列。應(yīng)用分區(qū)決策的原則時(shí)，您會(huì)發(fā)現(xiàn)這兩列都是合適的分區(qū)鍵候選者。在Oracle數(shù)據(jù)庫(kù)11g中，您可以相當(dāng)輕松地解決此問題。在該版本中，并不局限于范圍-散列和范圍-列表組合分區(qū)。您的選擇實(shí)際上沒有任何限制；您可以使用任何組合創(chuàng)建組合分區(qū)。在這個(gè)示例中，您可以決定根據(jù)product_code(因?yàn)樵摿芯哂懈嚯x散的值)對(duì)表進(jìn)行列表分區(qū)，然后根據(jù)state_code再次進(jìn)行列表分區(qū)。下面的代碼示例

26、顯示了如何實(shí)現(xiàn)該操作：createtablesales(sales_idnumber,product_codenumber,state_codevarchar2(2)partitionbylist(product_code)subpartitionbylist(state_code)(partitionp101values(101)(subpartitionp101_ctvalues(CT),subpartitionp101_nyvalues(NY),subpartitionp101_defvalues(default),partitionp201values(201)(subpartitio

27、np201_ctvalues(CT),subpartitionp201_nyvalues(NY),subpartitionp201_defvalues(default)選擇并不僅限于此處顯示的方法。您還可以創(chuàng)建列表-范圍組合分區(qū)。在上面的示例中，假設(shè)產(chǎn)品代碼不是離散的，而是在一個(gè)范圍內(nèi)。您將希望根據(jù)state_code進(jìn)行列表分區(qū)，然后再根據(jù)product_code劃分子分區(qū)。下面是實(shí)現(xiàn)該操作的代碼示例。createtablesales1(sales_idnumber,product_codenumber,state_codevarchar2(2)partitionbylist(state_c

28、ode)subpartitionbyrange(product_code)(partitionCTvalues(CT)(subpartitionct_100valueslessthan(101),subpartitionct_200valueslessthan(201),partitionNYvalues(NY)(subpartitionNY_100valueslessthan(101),subpartitionNY_200valueslessthan(201)您也可以創(chuàng)建范圍-范圍組合分區(qū)，如果您有兩個(gè)日期域，該方法將非常方便。例如，考慮一個(gè)用于銷售處理系統(tǒng)的表，該表包括一個(gè)交易日期和一個(gè)交

29、貨日期。您可能希望根據(jù)一個(gè)日期進(jìn)行范圍分區(qū)，然后再根據(jù)另一個(gè)日期進(jìn)行子范圍分區(qū)。該方案允許您根據(jù)日期進(jìn)行備份、存檔和清除。數(shù)據(jù)庫(kù)11g中可以創(chuàng)建以下類型的組合分區(qū)：范圍-范圍范圍-散列范圍-列表列表-范圍列表-散列列表-列表引用分區(qū)(11G)引用分區(qū)通過從父表繼承分區(qū)鍵(而非復(fù)制鍵列)，從而可以在邏輯上均分具有父子關(guān)系的表。分區(qū)鍵通過現(xiàn)有的父子關(guān)系解析，由現(xiàn)行的主鍵或外鍵約束實(shí)施。邏輯相關(guān)性還可以自動(dòng)級(jí)聯(lián)分區(qū)維護(hù)操作，從而使應(yīng)用程序開發(fā)更輕松且更不易出錯(cuò)。下面是設(shè)計(jì)分區(qū)方案過程中的一個(gè)典型問題：并非所有表都具有您需要根據(jù)其進(jìn)行分區(qū)的列。假設(shè)您要?jiǎng)?chuàng)建一個(gè)銷售系統(tǒng)，該系統(tǒng)包括兩個(gè)簡(jiǎn)單的表(sale

30、s和customers)：createtablecustomers(cust_idnumberprimarykey,cust_namevarchar2(200),ratingvarchar2(1)notnull)partitionbylist(rating)(partitionpAvalues(A),partitionpBvalues(B);sales表的創(chuàng)建如下所示。它是customers表的一個(gè)子表。createtablesales(sales_idnumberprimarykey,cust_idnumbernotnull,sales_amtnumber,constraintfk_sale

31、s_01foreignkey(cust_id)referencescustomers);理想情況下，您希望用相同的方式對(duì)sales表和customers表分區(qū)：根據(jù)rating列進(jìn)行列表分區(qū)。但有一個(gè)嚴(yán)重問題：sales表沒有名為rating的列！那么如何根據(jù)一個(gè)不存在的列進(jìn)行分區(qū)呢？在Oracle數(shù)據(jù)庫(kù)11g中，您可以使用一個(gè)稱為引用分區(qū)的新特性。下面的示例顯示了如何將該特性應(yīng)用于sales表：createtablesales(sales_idnumberprimarykey,cust_idnumbernotnull,sales_amtnumber,constraintfk_sales_01

32、foreignkey(cust_id)referencescustomers)partitionbyreference(fk_sales_01);這段代碼創(chuàng)建了與父表customers中相同的分區(qū)。注意，雖然沒有名為rating的列，但仍根據(jù)該列對(duì)表進(jìn)行了分區(qū)。partitionbyreference(fk_sales_01)子句包括了分區(qū)定義中的外鍵名。該語句指示Oracle數(shù)據(jù)庫(kù)11g確認(rèn)通過父表(在該示例中為customers)中使用的方案進(jìn)行了分區(qū)。注意cust_id列的NOTNULL約束；這是引用分區(qū)所必需的。如果您檢查sales表中分區(qū)的分區(qū)邊界：SQLselectpartitio

33、n_name,high_valuefromuser_tab_partitionswheretable_name=SALES;PARTITION_NAMEHIGH_VALUEPAPB高值為空，這意味著此處的邊界派生自父表。分區(qū)的名稱與父表中的名稱相同。您可以通過查詢user_part_tables視圖來檢查分區(qū)的類型。一個(gè)名為ref_ptn_constraint_name的特殊列顯示了外鍵約束名稱。SQLselecttable_name,partitioning_type,ref_ptn_constraint_name2fromuser_part_tables3wheretable_namein

34、(CUSTOMERS,SALES);TABLE_NAMEPARTITIONREF_PTN_CONSTRAINT_NAMECUSTOMERSLISTSALESREFERENCEFK_SALES_01如果您希望按照父表分區(qū)的方式對(duì)子表進(jìn)行分區(qū)，但沒有相同的列，您又不想僅僅為了分區(qū)而引入這些列，此時(shí)引用分區(qū)將非常方便。而且，您不必針對(duì)每個(gè)子表顯式聲明一個(gè)很長(zhǎng)的分區(qū)子句。間隔分區(qū)(11G)范圍分區(qū)允許您根據(jù)分區(qū)鍵列的值的范圍創(chuàng)建分區(qū)。下面是一個(gè)按范圍分區(qū)的表的示例：createtablesales6(sales_idnumber,sales_dtdate)partitionbyrange(sales_

35、dt)(partitionp0701valueslessthan(to_date(2007-02-01,yyyy-mm-dd),partitionp0702valueslessthan(to_date(2007-03-01,yyyy-mm-dd);您在此處僅針對(duì)2007年1月和2007年2月定義了分區(qū)，如果表中插入一條sales_dt在2007年3月的記錄，會(huì)發(fā)生什么情況？插入將失敗，并顯示以下錯(cuò)誤：ORA-14400:insertedpartitionkeydoesnotmaptoanypartition顯然，您需要針對(duì)2007年3月添加一個(gè)分區(qū)，然后才能插入一條記錄。但通常說起來容易做起來

36、難。您通常無法容忍事先創(chuàng)建大量分區(qū)，但其中很少一部分可能會(huì)產(chǎn)生此錯(cuò)誤。如果Oracle以某種方式自動(dòng)察覺到對(duì)新分區(qū)的需要，然后創(chuàng)建它們，這樣不是更好嗎？Oracle數(shù)據(jù)庫(kù)llg可以，它可以使用一個(gè)稱為間隔分區(qū)的特性。此時(shí)，您不必定義分區(qū)及它們的邊界，只需定義一個(gè)定義了每個(gè)分區(qū)邊界的間隔。下面是使用間隔分區(qū)的示例：createtablesales6(sales_idnumber,sales_dtdate)partitionbyrange(sales_dt)interval(numtoyminterval(1,MONTH)(partitionp0701valueslessthan(to_date(

37、2007-02-01,yyyy-mm-dd);注意子句：interval后面跟著時(shí)間間隔。您在此處指示Oracle為每個(gè)月份創(chuàng)建一個(gè)時(shí)間間隔。您已經(jīng)為2007年1月的數(shù)據(jù)創(chuàng)建了名為p0701的初始分區(qū)。現(xiàn)在，假設(shè)您插入了一條包括2007年6月數(shù)據(jù)的記錄：SQLinsertintosales6values(1,01-jun-07);rowcreated.Oracle不會(huì)返回錯(cuò)誤，而是成功執(zhí)行該語句。那么這條記錄將轉(zhuǎn)向何處？p0701分區(qū)不能包括該記錄，我們沒有為2007年6月定義分區(qū)。但此時(shí)，如果您檢查該表的分區(qū)：SQLselectpartition_name,high_valuefromuse

38、r_tab_partitionswheretable_name=SALES6;PARTITION_NAMEHIGH_VALUEP0701TO_DATE(2007-02-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIASYS_P41TO_DATE(2007-07-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA注意名為SYS_P1、高值為2007年7月1日的分區(qū)，它最多可以容納到6月底的數(shù)據(jù)。該分區(qū)是由Oracle動(dòng)態(tài)創(chuàng)建的，并具有一個(gè)系統(tǒng)生成的名稱。現(xiàn)在，假設(shè)您輸入一個(gè)小于最

39、高值的值，如2007年5月1日。理想情況下，它應(yīng)該具有自己的分區(qū)，因?yàn)槟姆謪^(qū)時(shí)間間隔是一個(gè)月。SQLinsertintosales6values(1,01-may-07);rowcreated.SQLselectpartition_name,high_valuefromuser_tab_partitionswheretable_name=SALES6;PARTITION_NAMEHIGH_VALUEP0701TO_DATE(2007-02-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIASYS_P41TO_DATE(2007-07-

40、0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIASYS_P42TO_DATE(2007-06-0100:00:00,SYYYY-MM-DDHH24:MI:SS,NLS_CALENDAR=GREGORIA注意新分區(qū)SYS_P42,其上限為6月1日，因此該分區(qū)可以保留2006年5月的數(shù)據(jù)。該分區(qū)是通過拆分SYS_P41分區(qū)創(chuàng)建的(針對(duì)6月份)。因此，當(dāng)您定義一個(gè)間隔分區(qū)方案時(shí)，Oracle會(huì)自動(dòng)創(chuàng)建和維護(hù)分區(qū)。如果您希望將分區(qū)存儲(chǔ)在特定表空間中，可以使用storein子句執(zhí)行該操作：interval(numtoyminterval(1,M

41、ONTH)storein(TS1,TS2,TS3)該子句以循環(huán)方式將分區(qū)存儲(chǔ)在表空間TS1、TS2和TS3中。應(yīng)用程序開發(fā)人員如何定位特定分區(qū)？一種方法是知道名稱，這種方法可能不可行,即使您知道名稱，這種方法也非常容易出錯(cuò)。為了便于訪問特定分區(qū)，Oracle數(shù)據(jù)庫(kù)11g為分區(qū)SQL提供了一個(gè)新語法：SQLselect*fromsales6partitionfor(to_date(15-may-2007,dd-mon-yyyy);SALES_IDSALES_DT101-MAY-07注意新子句for(值)，它允許您直接引用分區(qū)，而不必通過它們的確切名稱進(jìn)行顯式調(diào)用。如果您希望截?cái)嗷騽h除一個(gè)分區(qū)，可

42、以調(diào)用這個(gè)擴(kuò)展的分段語法。以此方式創(chuàng)建表之后，DBA_PART_TABLES視圖中的PARTITIONING_TYPE列會(huì)顯示時(shí)間間隔。根據(jù)虛擬列分區(qū)(11G)我們來看另一個(gè)常見問題。在名為SQLdescsalesNamesales的表中，您具有以下列Null?TypeSALES_IDCUST_IDSALES_AMTNOTNULLNUMBERNOTNULLNUMBERNUMBER假設(shè)您希望按照某個(gè)允許您進(jìn)行清除的方案對(duì)該表進(jìn)行分區(qū)，并且基于銷售額進(jìn)行存檔。以下是銷售的四個(gè)類別：如果sale_amt為且cust_id為則sale_category為0-10000任何內(nèi)容LOW10001-1000

43、000-100LOW10001-100000101-200MEDIUM10001-100000200HIGH100001-10000000-100MEDIUM100001-1000000101-200HIGH100001-1000000200ULTRA1000000任何內(nèi)容ULTRA您希望根據(jù)sale_category列對(duì)該表進(jìn)行分區(qū)，但有一個(gè)問題：沒有名為sale_category的列。這是您從sale_amt列派生的列。那么您如何對(duì)該表進(jìn)行分區(qū)呢？在Oracle的早期版本中，您可能已經(jīng)在表中插入了名為sale_category的新列，并使用一個(gè)觸發(fā)器用表中所示的邏輯填充該列。但是由于觸發(fā)器

44、，這個(gè)新列的存在可能會(huì)導(dǎo)致其他性能影響。在Oracle數(shù)據(jù)庫(kù)11g中，一個(gè)稱為虛擬列的新特性使您能夠創(chuàng)建一個(gè)并不存儲(chǔ)在表中的列，但在運(yùn)行時(shí)將計(jì)算該列。您還可以根據(jù)該列進(jìn)行分區(qū)。使用此特性，對(duì)該表進(jìn)行分區(qū)就變得輕而易舉。createtablesales(sales_idnumber,cust_idnumber,sales_amtnumber,sale_categoryvarchar2(6)generatedalwaysas(casewhensales_amt10000andsales_amt=100000thencasewhencust_id100000andsales_amt=1000000t

45、hencasewhencust_idinsertintosales(sales_id,cust_id,sales_amt)values(1,1,100);1rowcreated.SQLinsertintosales(sales_id,cust_id,sales_amt)values(2,1,1500);1rowcreated.SQLinsertintosales(sales_id,cust_id,sales_amt)values(3,102,1500);1rowcreated.SQLinsertintosales(sales_id,cust_id,sales_amt)values(4,102,

46、10000);1rowcreated.SQLcommit;Commitcomplete.注意，您未輸入sale_category的值?，F(xiàn)在，如果您檢查p_low中的記錄，將看到正確的記錄：SQLselect*fromsalespartition(p_low);SALES_IDCUST_IDSALES_AMTSALE_C11100LOW該記錄放在相應(yīng)的分區(qū)中。根據(jù)虛擬列分區(qū)使您能夠創(chuàng)建對(duì)業(yè)務(wù)重要的分區(qū)，即使列本身是不存在的。這里，您已經(jīng)對(duì)虛擬列使用了一個(gè)非常簡(jiǎn)單的計(jì)算方法，但它也可以如您希望的那樣復(fù)雜。在這些情況下，根據(jù)虛擬列進(jìn)行分區(qū)將變得更有價(jià)值。交換分區(qū)（不作為一個(gè)分區(qū)形式，是一個(gè)轉(zhuǎn)換工具）

47、Exchangepartition提供了一種方式，讓你在表與表或分區(qū)與分區(qū)之間遷移數(shù)據(jù)，注意不是將表轉(zhuǎn)換成分區(qū)或非分區(qū)的形式，而僅只是遷移表中數(shù)據(jù)（互相遷移），由于其號(hào)稱是采用了更改數(shù)據(jù)字典的方式，因此效率最高（幾乎不涉及io操作）。Exchangepartition適用于所有分區(qū)格式，你可以將數(shù)據(jù)從分區(qū)表遷移到非分區(qū)表，也可以從非分區(qū)表遷移至分區(qū)表，或者從hashpartition到rangepartition諸如此類吧。其語法很簡(jiǎn)單：altertabletbnamelexchangepartition/subpartitionptnamewithtabletbname2;注意：在將未分區(qū)表

48、的數(shù)據(jù)遷移到分區(qū)表中時(shí)，可能出現(xiàn)ora-14099的錯(cuò)誤，雖然可以用withoutvalidation去解決，但是此時(shí)進(jìn)入分區(qū)表的數(shù)據(jù)可能不符合分區(qū)規(guī)則。所以withoutvalidation定要慎用。l涉及交換的兩表之間表結(jié)構(gòu)必須一致，除非附加withvalidation子句;l如果是從非分區(qū)表向分區(qū)表做交換，非分區(qū)表中的數(shù)據(jù)必須符合分區(qū)表中指定分區(qū)的規(guī)則，除非附加withoutvalidation子句；l如果從分區(qū)表向分區(qū)表做交換，被交換的分區(qū)的數(shù)據(jù)必須符合分區(qū)規(guī)則，除非附加withoutvalidation子句；lGlobal索引或涉及到數(shù)據(jù)改動(dòng)了的global索引分區(qū)會(huì)被置為unusa

49、ble，除非附加updateindexes子句。提示：一旦附加了withoutvalidation子句，則表示不再驗(yàn)證數(shù)據(jù)有效性，因此指定該子句時(shí)務(wù)必慎重。分區(qū)表操作以上了解了三種分區(qū)表的建表方法，下面將使用實(shí)際的數(shù)據(jù)并針對(duì)按日期的范圍分區(qū)來測(cè)試分區(qū)表的數(shù)據(jù)記錄的操作。插入記錄：SQLinsertintodinya_testvalues(1,12,BOOKS,sysdate);rowcreated.SQLinsertintodinya_testvalues(2,12,BOOKS,sysdate+30);rowcreated.SQLinsertintodinya_testvalues(3,12,

50、BOOKS,to_date(2006-05-30,yyyy-mm-dd);rowcreated.SQLinsertintodinya_testvalues(4,12,BOOKS,to_date(2007-06-23,yyyy-mm-dd);rowcreated.SQLinsertintodinya_testvalues(5,12,BOOKS,to_date(2011-02-26,yyyy-mm-dd);1rowcreated.SQLinsertintodinya_testvalues(6,12,BOOKS,to_date(2011-04-30,yyyy-mm-dd);1rowcreated.S

51、QLcommit;Commitcomplete.SQL按上面的建表結(jié)果，2006年前的數(shù)據(jù)將存儲(chǔ)在第一個(gè)分區(qū)part_01上，而2006年到2010年的交易數(shù)據(jù)將存儲(chǔ)在第二個(gè)分區(qū)part_02上，2010年以后的記錄存儲(chǔ)在第三個(gè)分區(qū)part_03上。查詢分區(qū)表記錄：SQLselect*fromdinya_testpartition(part_O1);TRANSACTIONIDITEMIDITEMDESCRIPTIONTRANSACTIONDATE12BOOKS2005-1-1414:19:12BOOKS2005-2-1314:19:SQLSQLselect*fromdinya_testpart

52、ition(part_02);TRANSACTIONIDITEMIDITEMDESCRIPTIONTRANSACTIONDATE12BOOKS2006-5-3012BOOKS2007-6-23SQLSQLselect*fromdinya_testpartition(part_03);TRANSACTIONIDITEMIDITEMDESCRIPTIONTRANSACTIONDATE12BOOKS2011-2-2612BOOKS2011-4-30SQL從查詢的結(jié)果可以看出，插入的數(shù)據(jù)已經(jīng)根據(jù)交易時(shí)間范圍存儲(chǔ)在不同的分區(qū)中。這里是指定了分區(qū)的查詢，當(dāng)然也可以不指定分區(qū)，直接執(zhí)行select*from

53、dinya_test查詢?nèi)坑涗?。在也檢索的數(shù)據(jù)量很大的時(shí)候，指定分區(qū)會(huì)大大提高檢索速度。更新分區(qū)表的記錄SQLupdatedinya_testpartition(part_01)tsett.item_description=DESKwheret.transaction_id=1;1rowupdated.SQLcommit;Commitcomplete.SQL這里將第一個(gè)分區(qū)中的交易ID=1的記錄中的item_description字段更新為“DESK”，可以看到已經(jīng)成功更新了一條記錄。但是當(dāng)更新的時(shí)候指定了分區(qū)，而根據(jù)查詢的記錄不在該分區(qū)中時(shí)，將不會(huì)更新數(shù)據(jù)，請(qǐng)看下面的例子：SQLupdat

54、edinya_testpartition(part_01)tsett.item_description=DESKwheret.transaction_id=6;0rowsupdated.SQLcommit;Commitcomplete.SQL指定了在第一個(gè)分區(qū)中更新記錄，但是條件中限制交易ID為6，而查詢?nèi)恚灰譏D為6的記錄在第三個(gè)分區(qū)中，這樣該條語句將不會(huì)更新記錄。刪除分區(qū)表記錄：SQLdeletefromdinya_testpartition(part_02)twheret.transactiond=4;1rowdeleted.SQLcommit;Commitcomplete.SQL上

55、面例子刪除了第二個(gè)分區(qū)part_02中的交易記錄ID為4的一條記錄，和更新數(shù)據(jù)相同，如果指定了分區(qū)，而條件中的數(shù)據(jù)又不在該分區(qū)中時(shí)，將不會(huì)刪除任何數(shù)據(jù)。分區(qū)表索引的使用分區(qū)表和一般表一樣可以建立索引，分區(qū)表可以創(chuàng)建局部索引和全局索引。當(dāng)分區(qū)中出現(xiàn)許多事務(wù)并且要保證所有分區(qū)中的數(shù)據(jù)記錄的唯一性時(shí)采用全局索引。局部索引分區(qū)的建立就是索引信息的存放位置依賴于父表的Partition信息，換句話說創(chuàng)建這樣的索引必須保證父表是Partition索引信息存放在父表的分區(qū)所在的表空間。但是僅可以創(chuàng)建在父表為HashTable或者composite分區(qū)表的。LOCALSTOREIN(tablespace)僅可

56、以創(chuàng)建在父表為HashTable或者composite分區(qū)表的。并且指定的分區(qū)數(shù)目要與父表的分區(qū)數(shù)目要一致LOCALSTOREIN(tablespace)(PARTITIONpartitionLOGGING|NOLOGGINGTABLESPACEtablespace|DEFAULTPCTFREEintPCTUSEDintINITRANSintMAXTRANSintSTORAGEstorage_clauseSTOREINtablespace_name|DEFAULTSUBPARTITIONsubpartitionTABLESPACEtablespace)索引信息存放在父表的分區(qū)所在的表空間，這種

57、語法最簡(jiǎn)單，也是最常用的分區(qū)索引創(chuàng)建方式。Local并且指定的Partition數(shù)目要與父表的Partition要一致LOCAL(PARTITIONpartitionLOGGING|NOLOGGINGTABLESPACEtablespace|DEFAULTPCTFREEintPCTUSEDintINITRANSintMAXTRANSintSTORAGEstorage_clauseSTOREINtablespace_name|DEFAULTSUBPARTITIONsubpartitionTABLESPACEtablespace)SQLcreateindexdinya_idx_tondinya_t

58、est(itemd)TOC o 1-5 h zlocal(partitionidx_1tablespacedinya_space01,partitionidx_2tablespacedinya_space02,partitionidx_3tablespacedinya_space03);Indexcreated.SQL看查詢的執(zhí)行計(jì)劃，從下面的執(zhí)行計(jì)劃可以看出，系統(tǒng)已經(jīng)使用了索引SQLselect*fromdinya_testpartition(part_O1)twheret.item_id=12;ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Co

59、st=2Card=1Bytes=187)0TABLEACCESS(BYLOCALINDEXROWID)OFDINYA_TEST(Cost=Card=1Bytes=187)1INDEX(RANGESCAN)OFDINYA_IDX_T(NON-UNIQUE)(Cost=1Card=1)Statistics0recursivecalls0dbblockgets4consistentgets0physicalreads0redosize334bytessentviaSQL*Nettoclient309bytesreceivedviaSQL*Netfromclient2SQL*Netroundtrips

60、to/fromclientsorts(memory)0sorts(disk)rowsprocessedSQL全局索引分區(qū)的建立索引信息的存放位置與父表的Partition信息完全不相干。甚至父表是不是分區(qū)表都無所謂的。語法如下：GLOBALPARTITIONBYRANGE(col_list)(PARTITIONpartitionVALUESLESSTHAN(value_list)LOGGING|NOLOGGINGTABLESPACEtablespace|DEFAULTPCTFREEintPCTUSEDintINITRANSintMAXTRANSintSTORAGEstorage_clause)