Oracle資料庫中分割槽表的操作方法（轉）

Oracle資料庫中分割槽表的操作方法[@more@]在大量業務資料處理的專案中，可以考慮使用分割槽表來提高應用系統的效能並方便資料管理，本文詳細介紹了分割槽表的使用。
　　
　　在大型的企業應用或企業級的資料庫應用中，要處理的資料量通常可以達到幾十到幾百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. 總結：
　　
　　需要說明的是，本文在舉例說名分割槽表事務操作的時候，都指定了分割槽，因為指定了分割槽，系統在執行的時候則只操作該分割槽的記錄，提高了資料處理的速度。不要指定分割槽直接運算元據也是可以的。在分割槽表上建索引及多索引的使用和非分割槽表一樣。此外，因為在維護分割槽的時候可能對分割槽的索引會產生一定的影響，可能需要在維護之後重建索引，相關內容請參考分割槽表索引部分的文件。