1.分割槽表基礎知識

摘自部落格：http://space.itpub.net/15779287

提供了分割槽以支援VLDB(Very Large DataBase)。透過對分割槽列的判斷，把分割槽列不同的記錄，放到不同的分割槽中。分割槽完全對應用透明。

       Oracle的分割槽表可以包括多個分割槽，每個分割槽都是一個獨立的段（SEGMENT），可以存放到不同的表空間中。查詢時可以透過查詢表來訪問各個分割槽中的資料，也可以透過在查詢時直接指定分割槽的方法來進行查詢。

 

When to Partition a Table什麼時候需要分割槽表，官網的2個建議如下：

（1）Tables greater than 2GB should always be considered for partitioning.

（2）Tables containing historical , in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

 

在oracle 中最多支援：1024k-1個分割槽：

      Tables can be partitioned into up to 1024K-1 separate partitions

 

聯機文件上有關分割槽表和索引的說明：

      Partitioned Tables and Indexes

       

 

分割槽提供以下優點：

       （1）由於將資料分散到各個分割槽中，減少了資料損壞的可能性；

       （2）可以對單獨的分割槽進行和恢復；

       （3）可以將分割槽對映到不同的物理磁碟上，來分散IO；

       （4）提高可性、可用性和效能。

Oracle 10g提供了以下幾種分割槽型別：

       （1）範圍分割槽（range）；

       （2）雜湊分割槽（hash）；

       （3）列表分割槽（list）；

       （4）範圍－雜湊複合分割槽（range-hash）；

       （5）範圍－列表複合分割槽（range-list）。

Range分割槽：

　　Range分割槽是應用範圍比較廣的表分割槽方式，它是以列的值的範圍來做為分割槽的劃分條件，將記錄存放到列值所在的range分割槽中。

       如按照時間劃分，2010年1月的資料放到a分割槽，2月的資料放到b分割槽，在建立的時候，需要指定基於的列，以及分割槽的範圍值。

       在按時間分割槽時，如果某些記錄暫無法預測範圍，可以建立maxvalue分割槽，所有不在指定範圍內的記錄都會被儲存到maxvalue所在分割槽中。

 

如：

create table pdba (id number, time date) partition by range (time)

(

partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue)

)

 

Hash分割槽：

　　對於那些無法有效劃分範圍的表，可以使用hash分割槽，這樣對於提高效能還是會有一定的幫助。hash分割槽會將表中的資料平均分配到你指定的幾個分割槽中，列所在分割槽是依據分割槽列的hash值自動分配，因此你並不能控制也不知道哪條記錄會被放到哪個分割槽中，hash分割槽也可以支援多個依賴列。

 

如：

create table test

(

transaction_id number primary key,

item_id number(8) not null

)

partition by hash(transaction_id)

(

partition part_01 tablespace tablespace01,

partition part_02 tablespace tablespace02,

partition part_03 tablespace tablespace03

);

在這裡，我們指定了每個分割槽的表空間。

 

List分割槽：

　　List分割槽也需要指定列的值，其分割槽值必須明確指定，該分割槽列只能有一個，不能像range或者hash分割槽那樣同時指定多個列做為分割槽依賴列，但它的單個分割槽對應值可以是多個。

　　在分割槽時必須確定分割槽列可能存在的值，一旦插入的列值不在分割槽範圍內，則插入/更新就會失敗，因此通常建議使用list分割槽時，要建立一個default分割槽儲存那些不在指定範圍內的記錄，類似range分割槽中的maxvalue分割槽。

在根據某欄位，如城市程式碼分割槽時，可以指定default，把非分割槽規則的資料，全部放到這個default分割槽。

如：

create table custaddr
(

  id                     varchar2(15 byte)   not null,

  areacode   varchar2(4 byte)
 )

partition by list (areacode)
( partition t_list025 values ('025'),  
 partition t_list372 values ('372') , 
 partition t_list510 values ('510'), 

partition p_other values (default)

)

組合分割槽：

    如果某表按照某列分割槽之後，仍然較大，或者是一些其它的需求，還可以透過分割槽內再建子分割槽的方式將分割槽再分割槽，即組合分割槽的方式。

　　組合分割槽呢在10g中有兩種：range-hash，range-list。注意順序，根分割槽只能是range分割槽，子分割槽可以是hash分割槽或list分割槽。

如：

create table test

(

transaction_id number primary key,

transaction_date date

)

partition by range(transaction_date) subpartition by hash(transaction_id)

subpartitions 3 store in (tablespace01,tablespace02,tablespace03)

(

partition part_01 values less than(to_date(’2009-01-01’,’yyyy-mm-dd’)),

partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),

partition part_03 values less than(maxvalue)

);

create table emp_sub_template (deptno number, empname varchar(32), grade number)  

     partition by range(deptno) subpartition by hash(empname)

     subpartition template

         (subpartition a tablespace ts1,

          subpartition b tablespace ts2,

          subpartition c tablespace ts3,

          subpartition d tablespace ts4

         )

    (partition p1 values less than (1000),

     partition p2 values less than (2000),

     partition p3 values less than (maxvalue)

    );

create table quarterly_regional_sales

      (deptno number, item_no varchar2(20),

       txn_date date, txn_amount number, state varchar2(2))

  tablespace ts4 partition by range (txn_date) subpartition by list (state)

      (partition q1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy'))

         (subpartition q1_1999_northwest values ('or', 'wa'),

          subpartition q1_1999_southwest values ('az', 'ut', 'nm'),

          subpartition q1_1999_northeast values ('ny', 'vm', 'nj'),

          subpartition q1_1999_southeast values ('fl', 'ga'),

          subpartition q1_1999_northcentral values ('sd', 'wi'),

          subpartition q1_1999_southcentral values ('ok', 'tx')

         ),

       partition q2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy'))

         (subpartition q2_1999_northwest values ('or', 'wa'),

          subpartition q2_1999_southwest values ('az', 'ut', 'nm'),

          subpartition q2_1999_northeast values ('ny', 'vm', 'nj'),

          subpartition q2_1999_southeast values ('fl', 'ga'),

          subpartition q2_1999_northcentral values ('sd', 'wi'),

          subpartition q2_1999_southcentral values ('ok', 'tx')

         ),

       partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy'))

         (subpartition q3_1999_northwest values ('or', 'wa'),

          subpartition q3_1999_southwest values ('az', 'ut', 'nm'),

          subpartition q3_1999_northeast values ('ny', 'vm', 'nj'),

          subpartition q3_1999_southeast values ('fl', 'ga'),

          subpartition q3_1999_northcentral values ('sd', 'wi'),

          subpartition q3_1999_southcentral values ('ok', 'tx')

         ),

       partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy'))

         (subpartition q4_1999_northwest values ('or', 'wa'),

          subpartition q4_1999_southwest values ('az', 'ut', 'nm'),

          subpartition q4_1999_northeast values ('ny', 'vm', 'nj'),

          subpartition q4_1999_southeast values ('fl', 'ga'),

          subpartition q4_1999_northcentral values ('sd', 'wi'),

          subpartition q4_1999_southcentral values ('ok', 'tx')

         )

      );

    在Oracle 中，組合分割槽功能這塊有所增強，又增加了range-range,list-range,list-list,list-hash，並且 11g裡面還支援Interval分割槽和虛擬列分割槽。