Oracle 分區表 祥解（oracle partition table）__LINUX

 

一. 分區表理論知識

       Oracle提供了分區技術以支援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 data, 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 10g中最多支援：1024k-1個分區：

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

 

聯機文檔上有關分區表和索引的說明：

      Partitioned Tables and Indexes

       http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604

 

分區提供以下優點：

       （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 11g中，組合資料分割函數這塊有所增強，又增加了range-range,list-range,

list-list,list-hash，並且 11g裡面還支援Interval分區和虛擬列分區。

 

這塊可以參考Blog：

     Oracle 11g 新特性簡介

       http://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx

 

       分區表之 Interval分區和虛擬列按星期分區表

       http://blog.csdn.net/tianlesoftware/archive/2010/06/10/5662337.aspx

 

 

二.  普通錶轉分區表方法

 

將普通錶轉換成分區表有4種方法：

       1. Export/import method

       2. Insert with a subquery method

       3. Partition exchange method

       4. DBMS_REDEFINITION

具體參考：

       How to Partition a Non-partitioned Table [ID 1070693.6]

       http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

 

       邏輯匯出匯入這裡就不做說明，我們看看其他三種方法。

 

2.1  插入：Insert with a subquery method

     這種方法就是使用insert 來實現。當然在建立分區表的時候可以一起插入資料，也可以建立好後在insert 進去。這種方法採用DDL語句，不產生UNDO，只產生少量REDO，建表完成後資料已經在分布到各個分區中。

    

SQL> select count(*) from dba;

  COUNT(*)

----------

   2713235

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

 

SQL> select time_fee from dba where rownum<5;

TIME_FEE

-------------------

2011-02-17 19:29:09

2011-02-17 19:29:15

2011-02-17 19:29:18

2011-02-17 19:29:20

SQL>

 

2.1.1  Oracle 11g的Interval

       在11g裡的Interval建立，這種方法對沒有寫全的分區會自動建立。 比如我這裡唯寫了1月日期，如果插入的資料有其他月份的，會自動產生對應的分區。

 

/* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) */

CREATETABLE intervaldave

PARTITIONBYRANGE(time_fee)

   INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

   (PARTITION part1

       VALUESLESSTHAN(TO_DATE('01/12/2010','MM/DD/YYYY')))

AS

   SELECT   ID, TIME_FEE FROMDAVE;

 

 

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE';

 

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

INTERVALDAVE                   PART1

INTERVALDAVE                   SYS_P24

INTERVALDAVE                   SYS_P25

INTERVALDAVE                   SYS_P26

INTERVALDAVE                   SYS_P33

INTERVALDAVE                   SYS_P27

INTERVALDAVE                   SYS_P28

 

2.1.2  Oracle 10g 版本

       在10g裡面，我需要寫全所有的分區。

 

sql> create table pdba (id, time) partition by range (time)

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

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

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

  5    partition p4 values less than (maxvalue))

  6    as select id, time_fee from dba;

表已建立。

SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA';

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

PDBA                           P1

PDBA                           P2

PDBA                           P3

PDBA                           P4

sql> select count(*) from pdba partition (p1);

  count(*)

----------

   1718285

sql> select count(*) from pdba partition (p2);

  count(*)

----------

    183667

sql> select count(*) from pdba partition (p3);

  count(*)

----------

    188701

sql> select count(*) from pdba partition (p4);

  count(*)

----------

    622582

sql>

 

現在分區表已經建好了，但是表名不一樣，需要用rename對錶重新命名一下：

SQL> rename dba to dba_old;

表已重新命名。

SQL> rename pdba to dba;

表已重新命名。

SQL> select table_name,partition_name from user_tab_partitions where table_name='DBA';

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

DBA                            P1

DBA                            P2

DBA                            P3

DBA                            P4