Oracle分割槽表的使用

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

使用分割槽的優點：

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

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

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

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

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

1、範圍分割槽

2、Hash分割槽（雜湊分割槽）

3、複合分割槽

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

create tablespace dinya_space01datafile '/test/demo/oracle/demodata/dinya01.dnf' size 50M

create tablespace dinya_space01datafile '/test/demo/oracle/demodata/dinya02.dnf' size 50M

create tablespace dinya_space01datafile '/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.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.
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_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-23SQL>
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-30SQL>

從查詢的結果可以看出，插入的資料已經根據交易時間範圍儲存在不同的分割槽中。這裡是指定了分割槽的查詢，當然也可以不指定分割槽，直接執行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>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-84479/，如需轉載，請註明出處，否則將追究法律責任。

相關文章