Oracle中分區表的使用

2024-08-29 13:29:16

字體：大中小

來源：轉載

供稿：網友

最大的網站源碼資源下載站，

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

使用分區的優點：

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

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

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

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

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

        1、范圍分區

        2、hash分區（散列分區）

        3、復合分區

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

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     范圍分區
    范圍分區就是對數據表中的某個值的范圍進行分區，根據某個值的范圍，決定將該數據存儲在哪個分區上。如根據序號分區，根據業務記錄的創建日期進行分區等。

    需求描述：有一個物料交易表，表名：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-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查詢全部記錄。在也檢索的數據量很大的時候，指定分區會大大提高檢索速度。

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

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的一條記錄，和更新數據相同，如果指定了分區，而條件中的數據又不在該分區中時，將不會刪除任何數據。

3         分區表索引的使用：
分區表和一般表一樣可以建立索引，分區表可以創建局部索引和全局索引。當分區中出現許多事務并且要保證所有分區中的數據記錄的唯一性時采用全局索引。
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>

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>


4         分區表的維護：
    了解了分區表的建立、索引的建立、表和索引的使用后，在應用的還要經常對分區進行維護和管理。日常維護和管理的內容包括：增加一個分區，合并一個分區及刪除分區等等。下面以范圍分區為例說明增加、合并、刪除分區的一般操作：
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 錯誤。


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 錯誤。

4.3     刪除分區：
sql> alter table dinya_test drop partition part_01;

table altered.

sql>

    刪除分區表的一個分區后，查詢該表的數據時顯示，該分區中的數據已全部丟失，所以執行刪除分區動作時要慎重，確保先備份數據后再執行，或將分區合并。

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

上一篇：Oracle SQL依然無可替代--《Mastering Oracle SQL》

下一篇：淺談ORACLE 10G 閃回恢復區