Oracle資料庫分區表【操作方法】

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

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

使用分區的優點： 

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

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

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

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

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

·範圍分區 

·Hash分區（散列分區） 

·複合分區 

下面将以執行個體的方式分别對這三種分區方法來說明分區表的使用。為了測試友善，我們先建三個表空間。 

以下為引用的内容：

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> 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> 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> 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> 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将記錄散列地存儲在三個表空間中。 

1.2. 分區表操作 

以上了解了三種分區表的建表方法，下面将使用實際的資料并針對按日期的範圍分區來測試分區表的資料記錄的操作。 

1.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上。 

1.2.2. 查詢分區表記錄： 以下為引用的内容：

SQL> select * from dinya_test partition(part_01); 

TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE 

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

1 12 BOOKS 2005-1-14 14:19: 

2 12 BOOKS 2005-2-13 14:19: 

SQL> 

SQL> select * from dinya_test partition(part_02); 

TRANSACTION_ID ITEM_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_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE 

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

5 12 BOOKS 2011-2-26 

6 12 BOOKS 2011-4-30 

SQL> 

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

在也檢索的資料量很大的時候，指定分區會大大提高檢索速度。 

1.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的記錄在第三個分區中，這樣該條語句将不會更新記錄。 

1.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的一條記錄，和更新資料相同，如果指定了分區，而條件中的資料又不在該分區中時，将不會删除任何資料。

　1.3. 分區表索引的使用： 

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

　　 1.3.1. 局部索引分區的建立： 

以下為引用的内容：

SQL> create index dinya_idx_t on dinya_test(item_id) 

2 local 

3 ( 

4 partition idx_1 tablespace dinya_space01, 

5 partition idx_2 tablespace dinya_space02, 

6 partition idx_3 tablespace dinya_space03 

7 ); 

Index created. 

SQL> 

　　 看查詢的執行計劃，從下面的執行計劃可以看出，系統已經使用了索引： 

以下為引用的内容：

SQL> select * from dinya_test partition(part_01) t where t.item_id=12; 

Execution Plan 

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187) 

1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ’DINYA_TEST’ (Cost= 

2 Card=1 Bytes=187) 

2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1 

Card=1) 

Statistics 

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

0 recursive calls 

0 db block gets 

4 consistent gets 

0 physical reads 

0 redo size 

334 bytes sent via SQL*Net to client 

309 bytes received via SQL*Net from client 

2 SQL*Net roundtrips to/from client 

1 sorts (memory) 

0 sorts (disk) 

2 rows processed 

SQL> 

　　 1.3.2. 全局索引分區的建立

　　 全局索引建立時global 子句允許指定索引的範圍值，這個範圍值為索引字段的範圍值： 

以下為引用的内容：

SQL> create index dinya_idx_t on dinya_test(item_id) 

2 global partition by range(item_id) 

3 ( 

4 partition idx_1 values less than (1000) tablespace dinya_space01, 

5 partition idx_2 values less than (10000) tablespace dinya_space02, 

6 partition idx_3 values less than (maxvalue) tablespace dinya_space03 

7 ); 

Index created. 

SQL> 

　　 本例中對表的item_id字段建立索引分區，當然也可以不指定索引分區名直接對整個表建立索引，如： 

以下為引用的内容：

SQL> create index dinya_idx_t on dinya_test(item_id); 

Index created. 

SQL> 

　　 同樣的，對全局索引根據執行計劃可以看出索引已經可以使用： 

以下為引用的内容：

SQL> select * from dinya_test t where t.item_id=12; 

Execution Plan 

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561) 

1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF ’DINYA_TEST’ (Cost 

=2 Card=3 Bytes=561) 

2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1 

Card=3) 

Statistics 

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

5 recursive calls 

0 db block gets 

10 consistent gets 

0 physical reads 

0 redo size 

420 bytes sent via SQL*Net to client 

309 bytes received via SQL*Net from client 

2 SQL*Net roundtrips to/from client 

3 sorts (memory) 

0 sorts (disk) 

5 rows processed 

SQL>

1.4. 分區表的維護： 

了解了分區表的建立、索引的建立、表和索引的使用後，在應用的還要經常對分區進行維護和管理。日常維護和管理的内容包括：增加一個分區，合并一個分區及删除分區等等。下面以範圍分區為例說明增加、合并、删除分區的一般操作：

1.4.1. 增加一個分區: 

以下為引用的内容：

SQL> alter table dinya_test 

2 add partition part_04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’)) 

tablespace dinya_spa 

ce03; 

Table altered. 

SQL> 

增加一個分區的時候，增加的分區的條件必須大于現有分區的最大值，否則系統将提示ORA-14074 partition bound must collate higher than that of the last partition 錯誤。 

1.4.2. 合并一個分區： 

以下為引用的内容：

SQL> alter table dinya_test merge partitions part_01,part_02 into partition part_02; 

Table altered. 

SQL> 

在本例中将原有的表的part_01分區和part_02分區進行了合并，合并後的分區為part_02,如果在合并的時候把合并後的分區定為 part_01的時候，系統将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 錯誤。 

1.4.3. 删除分區： 

以下為引用的内容：

SQL> alter table dinya_test drop partition part_01; 

Table altered. 

SQL> 

删除分區表的一個分區後，查詢該表的資料時顯示，該分區中的資料已全部丢失，是以執行删除分區動作時要慎重，確定先備份資料後再執行，或将分區合并。 

1.5. 總結： 

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

轉載于:https://blog.51cto.com/huangchaosuper/887522