Oracle 分區(qū)表的優(yōu)點_第1頁
Oracle 分區(qū)表的優(yōu)點_第2頁
Oracle 分區(qū)表的優(yōu)點_第3頁
Oracle 分區(qū)表的優(yōu)點_第4頁
Oracle 分區(qū)表的優(yōu)點_第5頁
已閱讀5頁,還剩27頁未讀 繼續(xù)免費閱讀

下載本文檔

版權說明:本文檔由用戶提供并上傳,收益歸屬內容提供方,若內容存在侵權,請進行舉報或認領

文檔簡介

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

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

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

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

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

6、(表的索引和數(shù)據存儲在一起)支持通過范圍、列表或哈希進行分區(qū),然而,混合分區(qū)在索引組織表上不受支持。/間隔分區(qū):在11g中才引入,間隔分區(qū)是對范圍分區(qū)的擴展,為等距范圍分區(qū)提供了自動化,分區(qū)創(chuàng)建為元數(shù)據,只有分區(qū)開始部分是不變的,附加的段是當數(shù)據抵達時才分配的,附加分區(qū)和本地索引是自動創(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ù)轉換一個數(shù)字為間隔一年至一個月的文字(年或月),間隔分區(qū)表可以有傳統(tǒng)的范圍和自動間隔部分,范圍分區(qū)表可以通過在ALTERTABLE命令中使用SETINTERVAL選項被擴展為間隔分區(qū)表。/REF分區(qū):這個分區(qū)方案假設關聯(lián)表能從相同的分區(qū)策略中受益,子表通過PK-FK(主鍵-外鍵)關系繼承主表的策略,它不需要分區(qū)鍵存儲在子表中,通過PARTITIONBYREFERENCE關鍵字指定,子表繼承主表的分區(qū)

8、策略。/基于列的虛擬分區(qū):在Oracle以前的版本中,只有分區(qū)鍵物理存在于表中才能對表進行分區(qū),在11g中引入一個新的特性“虛擬列”移除了這個限制,允許分區(qū)鍵通過使用一個或多個表的列的表達式進行定義,虛擬列僅作為元數(shù)據存儲。如:向表ACCOUNTS添加一個虛擬列: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中為物理視圖、物理視圖日志和索引提供建議類似,實際上,分區(qū)顧問是Oracle11g中SQL訪問顧問的一部分,這

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

11、高的分區(qū)中,MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高于其它分區(qū)中的任何分區(qū)鍵的值,也可以理解為高于任何分區(qū)中指定的VALUELESSTHEN的值,同時包括空值。范圍分區(qū)就是對數(shù)據表中的某個值的范圍進行分區(qū),根據某個值的范圍,決定將該數(shù)據存儲在哪個分區(qū)上。如根據序號分區(qū),根據業(yè)務記錄的創(chuàng)建日期進行分區(qū)等。根據序號分區(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.根據交易的序號,交易ID在三千萬以下的記錄將存儲在第一個表空間din

13、ya_space01中,分區(qū)名為:par_01,在三千萬到六千萬之間的記錄存儲在第二個表空間:dinya_space02中,分區(qū)名為:par_02,而交易ID在六千萬以上的記錄存儲在第三個表空間dinya_space03中,分區(qū)名為par_03.根據交易日期分區(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.這樣我們就分別建了以交易序號和交易日期來分區(qū)的分區(qū)表。每次插入數(shù)據的時候,系統(tǒng)將根據指定的

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

16、數(shù)據根據交易ID散列地存放在指定的三個表空間中: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ū)最主要的機制是根據hash算法來計算具體某條紀錄應該插入到哪個分區(qū)中,hash算法中最重要的是hash函數(shù),Oracle中如果你要使用hash分區(qū),只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用2的n次方,這樣可以使得各個分區(qū)間數(shù)據分布更加均勻.復合分區(qū)有時候我們需要根據范圍分區(qū)后,每個分區(qū)內的數(shù)據再散列地分布在幾個表空間中,這樣我們就要使用復合分區(qū)。復合分區(qū)是先使用范圍分區(qū),然后在每個分區(qū)內再使用散列分區(qū)的一種分區(qū)方法,如將物料交易的記錄按時間分區(qū),然后每個分區(qū)中的數(shù)據分三個子分區(qū),將數(shù)據散列地存儲在三個指定的表空間

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.該例中,先是根據交易日期進行范圍分區(qū),然后根據交易的ID將記錄散列地存儲在三個表空間中。列表分區(qū)(91以上)該分區(qū)的特點是某列的值只有幾個,基于這樣的特點我們可以采用列表分區(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ū),表首先按某列進行范圍分區(qū),然后再按某列進行列表分區(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)復合范圍散列分區(qū):這種分區(qū)是基于范圍分區(qū)和散列分區(qū),表首先按某列進行范圍分區(qū),然后再按某列進行散列分區(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);擴展的組合分區(qū)使用組合分區(qū)Oracle8i數(shù)據庫中引入的方案您可以在分區(qū)中創(chuàng)建子分區(qū),從而進一步細分表。但在該情況下,您只能通過散列子分區(qū)對已按范圍分區(qū)的表進行再次分區(qū)。Oracle9i中對組合分區(qū)進行了擴展,使之包括范圍-列表子分區(qū)。這些方案滿足了大多數(shù)環(huán)境(但并非所有環(huán)境)的需要。例如,假設您有一個名為SALES的包含多列的表,其中包括兩個特殊的列,它們是分區(qū)的候選列:sta

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

26、顯示了如何實現(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ū)。在上面的示例中,假設產品代碼不是離散的,而是在一個范圍內。您將希望根據state_code進行列表分區(qū),然后再根據product_code劃分子分區(qū)。下面是實現(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ū),如果您有兩個日期域,該方法將非常方便。例如,考慮一個用于銷售處理系統(tǒng)的表,該表包括一個交易日期和一個交

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

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

31、s_01foreignkey(cust_id)referencescustomers);理想情況下,您希望用相同的方式對sales表和customers表分區(qū):根據rating列進行列表分區(qū)。但有一個嚴重問題:sales表沒有名為rating的列!那么如何根據一個不存在的列進行分區(qū)呢?在Oracle數(shù)據庫11g中,您可以使用一個稱為引用分區(qū)的新特性。下面的示例顯示了如何將該特性應用于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的列,但仍根據該列對表進行了分區(qū)。partitionbyreference(fk_sales_01)子句包括了分區(qū)定義中的外鍵名。該語句指示Oracle數(shù)據庫11g確認通過父表(在該示例中為customers)中使用的方案進行了分區(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ū)的類型。一個名為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ū)的方式對子表進行分區(qū),但沒有相同的列,您又不想僅僅為了分區(qū)而引入這些列,此時引用分區(qū)將非常方便。而且,您不必針對每個子表顯式聲明一個很長的分區(qū)子句。間隔分區(qū)(11G)范圍分區(qū)允許您根據分區(qū)鍵列的值的范圍創(chuàng)建分區(qū)。下面是一個按范圍分區(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);您在此處僅針對2007年1月和2007年2月定義了分區(qū),如果表中插入一條sales_dt在2007年3月的記錄,會發(fā)生什么情況?插入將失敗,并顯示以下錯誤:ORA-14400:insertedpartitionkeydoesnotmaptoanypartition顯然,您需要針對2007年3月添加一個分區(qū),然后才能插入一條記錄。但通常說起來容易做起來

36、難。您通常無法容忍事先創(chuàng)建大量分區(qū),但其中很少一部分可能會產生此錯誤。如果Oracle以某種方式自動察覺到對新分區(qū)的需要,然后創(chuàng)建它們,這樣不是更好嗎?Oracle數(shù)據庫llg可以,它可以使用一個稱為間隔分區(qū)的特性。此時,您不必定義分區(qū)及它們的邊界,只需定義一個定義了每個分區(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后面跟著時間間隔。您在此處指示Oracle為每個月份創(chuàng)建一個時間間隔。您已經為2007年1月的數(shù)據創(chuàng)建了名為p0701的初始分區(qū)?,F(xiàn)在,假設您插入了一條包括2007年6月數(shù)據的記錄:SQLinsertintosales6values(1,01-jun-07);rowcreated.Oracle不會返回錯誤,而是成功執(zhí)行該語句。那么這條記錄將轉向何處?p0701分區(qū)不能包括該記錄,我們沒有為2007年6月定義分區(qū)。但此時,如果您檢查該表的分區(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ù)據。該分區(qū)是由Oracle動態(tài)創(chuàng)建的,并具有一個系統(tǒng)生成的名稱?,F(xiàn)在,假設您輸入一個小于最

39、高值的值,如2007年5月1日。理想情況下,它應該具有自己的分區(qū),因為您的分區(qū)時間間隔是一個月。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ù)據。該分區(qū)是通過拆分SYS_P41分區(qū)創(chuàng)建的(針對6月份)。因此,當您定義一個間隔分區(qū)方案時,Oracle會自動創(chuàng)建和維護分區(qū)。如果您希望將分區(qū)存儲在特定表空間中,可以使用storein子句執(zhí)行該操作:interval(numtoyminterval(1,M

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

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

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

44、,這個新列的存在可能會導致其他性能影響。在Oracle數(shù)據庫11g中,一個稱為虛擬列的新特性使您能夠創(chuàng)建一個并不存儲在表中的列,但在運行時將計算該列。您還可以根據該列進行分區(qū)。使用此特性,對該表進行分區(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該記錄放在相應的分區(qū)中。根據虛擬列分區(qū)使您能夠創(chuàng)建對業(yè)務重要的分區(qū),即使列本身是不存在的。這里,您已經對虛擬列使用了一個非常簡單的計算方法,但它也可以如您希望的那樣復雜。在這些情況下,根據虛擬列進行分區(qū)將變得更有價值。交換分區(qū)(不作為一個分區(qū)形式,是一個轉換工具)

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

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

49、ble,除非附加updateindexes子句。提示:一旦附加了withoutvalidation子句,則表示不再驗證數(shù)據有效性,因此指定該子句時務必慎重。分區(qū)表操作以上了解了三種分區(qū)表的建表方法,下面將使用實際的數(shù)據并針對按日期的范圍分區(qū)來測試分區(qū)表的數(shù)據記錄的操作。插入記錄: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按上面的建表結果,2006年前的數(shù)據將存儲在第一個分區(qū)part_01上,而2006年到2010年的交易數(shù)據將存儲在第二個分區(qū)part_02上,2010年以后的記錄存儲在第三個分區(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從查詢的結果可以看出,插入的數(shù)據已經根據交易時間范圍存儲在不同的分區(qū)中。這里是指定了分區(qū)的查詢,當然也可以不指定分區(qū),直接執(zhí)行select*from

53、dinya_test查詢全部記錄。在也檢索的數(shù)據量很大的時候,指定分區(qū)會大大提高檢索速度。更新分區(qū)表的記錄SQLupdatedinya_testpartition(part_01)tsett.item_description=DESKwheret.transaction_id=1;1rowupdated.SQLcommit;Commitcomplete.SQL這里將第一個分區(qū)中的交易ID=1的記錄中的item_description字段更新為“DESK”,可以看到已經成功更新了一條記錄。但是當更新的時候指定了分區(qū),而根據查詢的記錄不在該分區(qū)中時,將不會更新數(shù)據,請看下面的例子:SQLupdat

54、edinya_testpartition(part_01)tsett.item_description=DESKwheret.transaction_id=6;0rowsupdated.SQLcommit;Commitcomplete.SQL指定了在第一個分區(qū)中更新記錄,但是條件中限制交易ID為6,而查詢全表,交易ID為6的記錄在第三個分區(qū)中,這樣該條語句將不會更新記錄。刪除分區(qū)表記錄:SQLdeletefromdinya_testpartition(part_02)twheret.transactiond=4;1rowdeleted.SQLcommit;Commitcomplete.SQL上

55、面例子刪除了第二個分區(qū)part_02中的交易記錄ID為4的一條記錄,和更新數(shù)據相同,如果指定了分區(qū),而條件中的數(shù)據又不在該分區(qū)中時,將不會刪除任何數(shù)據。分區(qū)表索引的使用分區(qū)表和一般表一樣可以建立索引,分區(qū)表可以創(chuàng)建局部索引和全局索引。當分區(qū)中出現(xiàn)許多事務并且要保證所有分區(qū)中的數(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、語法最簡單,也是最常用的分區(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í)行計劃,從下面的執(zhí)行計劃可以看出,系統(tǒ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)

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網頁內容里面會有圖紙預覽,若沒有圖紙預覽就沒有圖紙。
  • 4. 未經權益所有人同意不得將文件中的內容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網僅提供信息存儲空間,僅對用戶上傳內容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內容本身不做任何修改或編輯,并不能對任何下載內容負責。
  • 6. 下載文件中如有侵權或不適當內容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

評論

0/150

提交評論