oracle分區表詳解____oracle

轉載自：http://blog.csdn.net/hijiankang/article/details/9173877/

一. 分區表理論知識

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) */

CREATE TABLE intervaldave

PARTITION BY RANGE (time_fee)

INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

(PARTITION part1

VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')))

AS

SELECT ID, TIME_FEE FROM DAVE;

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

2.2 . 交換分區：Partition exchange method

這種方法只是對資料字典中分區和表的定義進行了修改，沒有資料的修改或複製，效率最高。適用於包含大資料量的錶轉到分區表中的一個分區的操作。盡量在閑時進行操作。

交換分區的操作步驟如下：

1. 建立分區表，假設有2個分區，P1，P2.