Oracle資料庫 分表詳細執行個體

使用分區的優點：

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

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

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

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

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

　　1、定界分割

　　2、Hash分區（散列分區）

　　3、複合分區

　　下面將以執行個體的方式分別對這三種分區方法來說明分區表的使用。為了測試方便，我們先建三個資料表空間。

　　create tablespace dinya_space01

　　datafile '/test/demo/oracle/demodata/dinya01.dnf' size 50M

　　create tablespace dinya_space02

　　datafile '/test/demo/oracle/demodata/dinya02.dnf' size 50M

　　create tablespace dinya_space03

　　datafile '/test/demo/oracle/demodata/dinya03.dnf' size 50M

　　1分區表的建立：

　　1.1定界分割

　　定界分割就是對資料表中的某個值的範圍進行分區，根據某個值的範圍，決定將該資料儲存在哪個分區上。如根據序號分區，根據業務記錄的建立日期進行分區等。

　　需求描述：有一個物料交易表，表名：material_transactions。該表將來可能有千萬級的資料記錄數。要求在建該表的時候使用分區表。這時候我們可以使用序號分區三個區，每個區中預計儲存三千萬的資料，也可以使用日期分區，如每五年的資料存放區在一個分區上。

　　根據交易記錄的序號分區建表：

　　SQL> create table dinya_test

　　2  (

　　3 transaction_id number primary key,

　　4 item_id number(8) not null,

　　5 item_description varchar2(300),

　　6 transaction_date date  not null

　　7  )

　　8  partition by range (transaction_id)

　　9  (

　　10 partition part_01 values less than(30000000) tablespace dinya_space01,

　　11 partition part_02 values less than(60000000) tablespace dinya_space02,

　　12 partition part_03 values less than(maxvalue) tablespace dinya_space03

　　13  );

　　Table created.

　　SQL>

　　建表成功，根據交易的序號，交易ID在三千萬以下的記錄將儲存在第一個資料表空間dinya_space01中，分區名為:par_01，在三千萬到六千萬之間的記錄儲存在第二個資料表空間：dinya_space02中，分區名為：par_02，而交易ID在六千萬以上的記錄儲存在第三個資料表空間dinya_space03中，分區名為par_03.

　　根據交易日期分區建表：

　　SQL> create table dinya_test

　　2  (

　　3 transaction_id number primary key,

　　4 item_id number(8) not null,

　　5 item_description varchar2(300),

　　6 transaction_date date not null

　　7  )

　　8  partition by range (transaction_date)

　　9  (

　　10  partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dinya_space01,

　　11  partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace dinya_space02,

 

　　12  partition part_03 values less than(maxvalue) tablespace dinya_space03

　　13  );

　　Table created.

　　SQL>

　　這樣我們就分別建了以交易序號和交易日期來分區的分區表。每次插入資料的時候，系統將根據指定的欄位的值來自動將記錄儲存到制定的分區（資料表空間）中。

　　當然，我們還可以根據需求，使用兩個欄位的範圍分布來分區，如partition by range ( transaction_id ,transaction_date),分區條件中的值也做相應的改變，請讀者自行測試。

　　1.2Hash分區（散列分區）

　　散列分區為通過指定分區編號來均勻分布資料的一種分區類型，因為通過在I/O裝置上進行散列分區，使得這些分區大小一致。如將物料交易表的資料根據交易ID散列地存放在指定的三個資料表空間中：

　　SQL> create table dinya_test

　　2  (

　　3 transaction_id number primary key,

　　4 item_id number(8) not null,

　　5 item_description varchar2(300),

　　6 transaction_date date

　　7  )

　　8  partition by hash(transaction_id)

　　9  (

　　10 partition part_01 tablespace dinya_space01,

　　11 partition part_02 tablespace dinya_space02,

　　12 partition part_03 tablespace dinya_space03

　　13  );

　　Table created.

　　SQL>

　　建表成功，此時插入資料，系統將按transaction_id將記錄散列地插入三個分區中，這裡也就是三個不同的資料表空間中。

　　

　　

　　　1.3   複合分區

　　有時候我們需要根據定界分割後，每個分區內的資料再散列地分布在幾個資料表空間中，這樣我們就要使用複合分區。複合分區是先使用定界分割，然後在每個分區內再使用散列分區的一種分區方法，如將物料交易的記錄按時間分區，然後每個分區中的資料分三個子分區，將資料散列地儲存在三個指定的資料表空間中：

　　SQL> create table dinya_test

　　2  (

　　3 transaction_id number primary key,

　　4 item_id number(8) not null,

　　5 item_description varchar2(300),

　　6 transaction_date date

　　7  )

　　8  partition by range(transaction_date)subpartition by hash(transaction_id)

　　9 subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)

　　10  (

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

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

　　13 partition part_03 values less than(maxvalue)

　　14  );

　　Table created.

　　SQL>

　　該例中，先是根據交易日期進行定界分割，然後根據交易的ID將記錄散列地儲存在三個資料表空間中。

　　2分區表操作

　　以上瞭解了三種分區表的建表方法，下面將使用實際的資料並針對按日期的定界分割來測試分區表的資料記錄的操作。

　　2.1插入記錄：

　　SQL> insert into dinya_test values(1,12,'BOOKS',sysdate);

　　1 row created.

　　SQL> insert into dinya_test values(2,12, 'BOOKS',sysdate+30);

　　1 row created.

　　SQL> insert into dinya_test values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));

　　1 row created.

　　SQL> insert into dinya_test values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));

　　1 row created.SQL> insert into dinya_test values(5,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));

 

　　1 row created.

　　SQL> insert into dinya_test values(6,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));

　　1 row created.

　　SQL> commit;

　　Commit complete.

　　SQL>

　　按上面的建表結果，2006年前的資料將儲存在第一個分區part_01上，而2006年到2010年的交易資料將儲存在第二個分區part_02上，2010年以後的記錄儲存在第三個分區part_03上。

　　2.2查詢分區表記錄：

　　SQL> select * from dinya_test partition(part_01);

　　TRANSACTION_IDITEM_ID  ITEM_DESCRIPTION  TRANSACTION_DATE

　　--------------------------------------------------------------------------------

　　112  BOOKS  2005-1-14 14:19:

　　212  BOOKS  2005-2-13 14:19:

　　SQL>

　　SQL> select * from dinya_test partition(part_02);

　　TRANSACTION_IDITEM_ID ITEM_DESCRIPTION  TRANSACTION_DATE

　　--------------------------------------------------------------------------------

　　3 12  BOOKS   2006-5-30

　　4 12  BOOKS   2007-6-23

　　SQL>

　　SQL> select * from dinya_test partition(part_03);

　　TRANSACTION_IDITEM_IDITEM_DESCRIPTION TRANSACTION_DATE

　　--------------------------------------------------------------------------------

　　5 12BOOKS  2011-2-26

　　6 12BOOKS  2011-4-30

　　SQL>

　　從查詢的結果可以看出，插入的資料已經根據交易時間範圍儲存在不同的分區中。這裡是指定了分區的查詢，當然也可以不指定分區，直接執行select * from dinya_test查詢全部記錄。在也檢索的資料量很大的時候，指定分區會大大提高檢索速度。

　　2.3更新分區表的記錄：

　　SQL> update dinya_test partition(part_01) t set t.item_description='DESK' where t.transaction_id=1;

　　1 row updated.

　　SQL> commit;

　　Commit complete.

　　SQL>

　　這裡將第一個分區中的交易ID=1的記錄中的item_description欄位更新為“DESK”，可以看到已經成功更新了一條記錄。但是當更新的時候指定了分區，而根據查詢的記錄不在該分區中時，將不會更新資料，請看下面的例子：

　　SQL> update dinya_test partition(part_01) t set t.item_description='DESK' where t.transaction_id=6;

　　0 rows updated.

　　SQL> commit;

　　Commit complete.

　　SQL>

　　指定了在第一個分區中更新記錄，但是條件中限制交易ID為6，而查詢全表，交易ID為6的記錄在第三個分區中，這樣該條語句將不會更新記錄。

　　2.4刪除分區表記錄：

　　SQL> delete from dinya_test partition(part_02) t where t.transaction_id=4;

　　1 row deleted.

　　SQL> commit;

　　Commit complete.

　　SQL>

　　上面例子刪除了第二個分區part_02中的交易記錄ID為4的一條記錄，和更新資料相同，如果指定了分區，而條件中的資料又不在該分區中時，將不會刪除任何資料。

　　3分區表索引的使用：

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

　　3.1局部索引分割區的建立：

　　SQL> create index dinya_idx_t on dinya_test(item_id)

　　2  local

　　3  (