淺談Oracle資料庫分區表

標籤：

 

Oracle資料庫分區是作為Oracle資料庫效能最佳化的一種重要的手段和方法，之前，只聽過分區的大名，卻總未用過，最近簡單學習了一下，總結如下，不對之處，還希望朋友們多多指點，交流！

    1.資料表空間及分區表的概念
    2.表分區的具體作用
    3.表分區的優缺點
    4.表分區的幾種類型及操作方法
    5.對錶分區的維護性操作.

( 1.) 資料表空間及分區表的概念
    資料表空間：
       是一個或多個資料檔案的集合，所有的資料對象都存放在指定的資料表空間中，但主要存放的是表， 所以稱作資料表空間。
    分區表：
       當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分區。表進行分區後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個資料表空間(物理檔案上)，這樣查詢資料時，不至於每次都掃描整張表。

( 2.)表分區的具體作用

       Oracle的表資料分割函數通過改善可管理性、效能和可用性，從而為各式應用程式帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的效能大大提高。此外,分區還可以極大簡化常見的管理工作，分區是構建千MB資料系統或超高可用性系統的關鍵工具。

      資料分割函數能夠將表、索引或索引組織表進一步細分為段，這些資料庫物件的段叫做分區。每個分區有自己的名稱，還可以選擇自己的儲存特性。從資料庫管理員的角度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分區後的對象時有相當大的靈活性。但是，從應用程式的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 命令訪問分區後的表時，無需任何修改。

    什麼時候使用分區表：
    1、表的大小超過2GB。
    2、表中包含曆史資料，新的資料被增加都新的分區中。

( 3.)表分區的優缺點

     表分區有以下優點：

 　　1) 增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；

　　 2) 維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；

　　 3) 均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能；

　　 4) 改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。

　  缺點：
       分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了線上重定義表的功能。

( 4.)表分區的幾種類型及操作方法

　　　　Oracle資料庫提供對錶或索引的分區方法有三種：

　　　　　　ü 定界分割

　　　　　　ü Hash分區（散列分區）

　　　　　　ü 複合分區

　　1、定界分割詳細說明

　　定界分割就是對資料表中的某個值的範圍進行分區，根據某個值的範圍，決定將該資料存放區在哪個分區上。如根據序號分區，根據時間等來進行分區，也可以使用這兩個欄位共同來進行分區具體需要根據項目需求等原因。根據序號，比如小於2000000的放在part01, 2000000~4000000的放在part02。。。

create table AAA(

 id number primary key,  

 indate date not null

)partition by range(indate)(

 partition part_01 values less than(to_date(‘2006-01-01‘,‘yyyy-mm-dd‘))tablespace space01,  

 partition part_02 values less than(to_date(‘2010-01-01‘,‘yyyy-mm-dd‘))tablespace space02,

 partition part_03 values less than(maxvalue)tablespace space03

);

space01\ space02\ space03為建立的三個資料表空間，相當於把建立的一個大的表分在了3個不同的資料表空間的分區上了。

　　2、Hash分區（散列分區）詳細說明

     散列分區為通過指定分區編號來均勻分布資料的一種分區類型，因為通過在I/O裝置上進行散列分區，使得這些分區大小一致。也就是只命名分區名稱，這樣均勻進行資料分布。

create table AAA(

 id number primary key,  

 indate date not null

)partition by range( indate ) subpartition by hash( id )(

  partition part_01 tablespace space01,

  partition part_02 tablespace space02,

  partition part_03 tablespace space03
);

　　3、複合分區詳細說明

     有時候我們需要根據定界分割後，每個分區內的資料再散列地分布在幾個資料表空間中，這樣我們就要使用複合分區。複合分區是先使用定界分割，然後在每個分區內再使用

散列分區的一種分區方法。

partition by range(indate)subpartition by hash(id)

subpartitions 3 store in (space01, space02, space03) (

　　partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)),

　　partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),

　　partition part_03 values less than(maxvalue)

 );

( 5.) 有關表分區的一些維護性操作：

　　1、插入記錄：

　　　　insert into AAA values(1 ,sysdate);

　　　　insert into AAA values(2 ,to_date(‘2006-01-01‘,‘yyyy-mm-dd‘));

　　　　insert into AAA values(3 ,to_date(‘2011-01-01‘,‘yyyy-mm-dd‘));

　　2、查詢分區表記錄：select * from AAA partition(part_01);

　　3、更新分區表的記錄：update AAA partition(part_01) t set indate=’2015-01-30’  where t.id=1;

    　　但是當更新的時候指定了分區，而根據查詢的記錄不在該分區中時，將不會更新資料

　　4、刪除分區表記錄：delete from AAA partition(part_02) t where t.id=4;

    　　 如果指定了分區，而條件中的資料又不在該分區中時，將不會刪除任何資料。

　　5、增加一個分區：

  　　  alter table AAA add partition part_04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’)) tablespace dinya_spa ce03;

   　　 增加一個分區的時候，增加的分區的條件必須大於現有分區的最大值，否則系統將提示ORA-14074 partition bound must collate

   　　 higher than that of the last partition 錯誤。

　　6、合并一個分區：

    　　alter table AAA merge partitions part_01,part_02 into partition part_02;

    　　如果在合并的時候把合并後的分區定為part_01的時候，系統將提示ORA-14275 cannot reuse lower-bound partition as resulting

   　　 partition 錯誤。

　　7、刪除分區：alter table AAA drop partition part_01; 刪除分區表的一個分區後，查詢該表的資料時顯示，該分區中的資料已全部丟失，所以執行刪除分區動作時要謹慎，確保先備份資料後再執行，或將分區合并。

( 6.) 建立索引

    分區表和一般表一樣可以建立索引，分區表可以建立局部索引和全域索引。當分區中出現許多事務並且要保證所有分區中的資料記錄的唯一性時採用全域索引。

   1. 局部索引分割區的建立：

　　create index idx_t on AAA(id)  local (

　　　　partition idx_1 tablespace space01,

　　　　partition idx_2 tablespace space02,

　　　　partition idx_3 tablespace space03

　　);

　2. 全域索引建立時global 子句允許指定索引的範圍值，這個範圍值為索引欄位的範圍值：

　　create index idx_t on AAA(id) global partition by range(id) (

　　 　　partition idx_1 values less than (1000) tablespace space01,

　　　　 partition idx_2 values less than (10000) tablespace space02,

　　　　 partition idx_3 values less than (maxvalue) tablespace space03

　　 );

      當然也可以不指定索引分割區名直接對整個表建立索引:

　　create index idx_t on AAA(id);

　　

總結：
    需要說明的是，以上距離分區表事務操作的的時候，都指定了分區，因為指定了分區系統在執行的時候只操作了該分區的記錄，提高了資料的處理速度。不要指定分區直接操作資料也是可以的。在分區表上建索引及多索引的使用和非分區表一樣。此外，因為在維護分區的時候可能會對分區的索引會產生一定的影響，可能需要在維護之後重建索引，相關內容請參考分區表索引部分的文檔。

 

淺談Oracle資料庫分區表