Oracle資料庫中分區表的操作方法(適用於單表大資料量或T層級資料庫)

摘要：在大量業務資料處理的項目中，可以考慮使用分區表來提高應用系統的效能並方便資料管理，本文詳細介紹了分區表的使用。

　　在大型的公司專屬應用程式或企業級的資料庫應用中，要處理的資料量通常可以達到幾十到幾百GB，有的甚至可以到TB級。雖然儲存介質和資料處理技術的發 展也很快，但是仍然不能滿足使用者的需求，為了使使用者的大量的資料在讀寫操作和查詢中速度更快，Oracle提供了對錶和索引進行分區的技術，以改善大型應 用系統的效能。

　　使用分區的優點：

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

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

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

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

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

　　·范圍分區

　　·Hash分區（散列分區）

　　·複合分區

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

Sql代碼

1. create tablespace dinya_space01   
2. datafile ’/test/demo/oracle/demodata/dinya01.dnf’ size 50M   
3. create tablespace dinya_space01   
4. datafile ’/test/demo/oracle/demodata/dinya02.dnf’ size 50M   
5. create tablespace dinya_space01   
6. datafile ’/test/demo/oracle/demodata/dinya03.dnf’ size 50M    

create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya01.dnf’ size 50M create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya02.dnf’ size 50M create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya03.dnf’ size 50M 

 

　　1.1. 分區表的建立

　　1.1.1. 定界分割

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

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

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

Sql代碼

1. SQL> create table dinya_test   
2. 2 (   
3. 3 transaction_id number primary key,   
4. 4 item_id number(8) not null,   
5. 5 item_description varchar2(300),   
6. 6 transaction_date date not null   
7. 7 )   
8. 8 partition by range (transaction_id)   
9. 9 (   
10. 10 partition part_01 values less than(30000000) tablespace dinya_space01,   
11. 11 partition part_02 values less than(60000000) tablespace dinya_space02,   
12. 12 partition part_03 values less than(maxvalue) tablespace dinya_space03   
13. 13 );   
14. Table created.   

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. 

 

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

　　dinya_space02中，分區名為：par_02，而交易ID在六千萬以上的記錄儲存在第三個資料表空間dinya_space03中，分區 名為par_03.

　　根據交易日期分區建表：

Sql代碼

1. SQL> create table dinya_test   
2. 2 (   
3. 3 transaction_id number primary key,   
4. 4 item_id number(8) not null,   
5.   
6. 5 item_description varchar2(300),   
7. 6 transaction_date date not null   
8. 7 )   
9. 8 partition by range (transaction_date)   
10. 9 (   
11. 10 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’))   
12. tablespace dinya_space01,   
13. 11 partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’))   
14. tablespace dinya_space02,   
15. 12 partition part_03 values less than(maxvalue) tablespace dinya_space03   
16. 13 );   
17. Table created.    

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. 

 

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

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

　　1.1.2. Hash分區（散列分區）

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

Sql代碼

1. SQL> create table dinya_test   
2. 2 (   
3. 3 transaction_id number primary key,   
4. 4 item_id number(8) not null,   
5. 5 item_description varchar2(300),   
6. 6 transaction_date date   
7. 7 )   
8. 8 partition by hash(transaction_id)   
9. 9 (   
10. 10 partition part_01 tablespace dinya_space01,   
11. 11 partition part_02 tablespace dinya_space02,   
12. 12 partition part_03 tablespace dinya_space03   
13. 13 );   
14. Table created.    

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. 

 

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

　　1.1.3. 複合分區

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

Sql代碼

1. SQL> create table dinya_test   
2. 2 (   
3. 3 transaction_id number primary key,   
4. 4 item_id number(8) not null,   
5. 5 item_description varchar2(300),   
6. 6 transaction_date date   
7. 7 )   
8. 8 partition by range(transaction_date)subpartition by hash(transaction_id)   
9. 9 subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)   
10. 10 (   
11. 11 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)),   
12. 12 partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),   
13. 13 partition part_03 values less than(maxvalue)   
14. 14 );   
15. Table created.    

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. 

 

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

 

 

原始文章連結：

http://werwolf.javaeye.com/blog/672820