Oracle數據庫中分區表的操作方法

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

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

　　使用分區的優點：

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

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

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

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

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

　　·范圍分區

　　·Hash分區（散列分區）

　　·復合分區

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

clearcase/" target="_blank" >cc66 width="90%" align=center bgColor=#dadacf border=1>

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查詢全部記錄。

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

　　

原文轉自：http://www.anti-gravitydesign.com