談談怎麼實現Oracle資料庫分區表

Oracle資料庫分區是作為Oracle資料庫效能最佳化的一種重要的手段和方法，做手頭的項目以前，只聆聽過分區的大名，感覺特神秘，看見某某高手在討論會上夸夸其談時，真是罵自己學藝不精，最近作GPS方面的項目，處理的資料量達到了幾十GB，為了滿足系統的即時性要求，必須提高資料的查詢效率，這樣就必須通過分區，以解燃眉之急！

先說說分區的好處吧！

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

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

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

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

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

ü 定界分割

ü Hash分區（散列分區）

ü 複合分區

 

一、定界分割詳細說明

定界分割就是對資料表中的某個值的範圍進行分區，根據某個值的範圍，決定將該資料存放區在哪個分區上。如根據序號分區，根據時間等來進行分區。根據序號，比如小於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個不同的資料表空間的分區上了。

 

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

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

 

三、複合分區詳細說明

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

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)
 );

 

四、分區表操作

1、插入記錄：insert into AAA values(1 ,sysdate);

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

3、更新分區表的記錄：update AAA partition(part_01) t set indate=’’where id=1; 但是當更新的時候指定了分區，而根據查詢的記錄不在該分區中時，將不會更新資料

4、刪除分區表記錄：delete from AAA partition(part_02) t where 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; 刪除分區表的一個分區後，查詢該表的資料時顯示，該分區中的資料已全部丟失，所以執行刪除分區動作時要謹慎，確保先備份資料後再執行，或將分區合并。

 

五、建立索引

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

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);