簡單說說Oracle分區

作者：Tracy 2011-05-31 14:06:10

ORACLE的分區是一種處理超大型表、索引等的技術。分區是一種“分而治之”的技術，通過將大表和索引分成可以管理的小塊，從而避免了對每個表作為一個大的、單獨的對象進行管理，為大量數據提供了可伸縮的性能。

一、簡介

ORACLE的分區是一種處理超大型表、索引等的技術。分區是一種“分而治之”的技術，通過將大表和索引分成可以管理的小塊，從而避免了對每個表作為一個大的、單獨的對象進行管理，為大量數據提供了可伸縮的性能。分區通過將操作分配給更小的存儲單元，減少了需要進行管理操作的時間，并通過增強的并行處理提高了性能，通過屏蔽故障數據的分區，還增加了可用性。

二、優缺點

優點：

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

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

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

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

缺點：

分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了在線重定義表的功能。

三、分區方法

范圍分區：

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

Hash分區（散列分區）：

散列分區為通過指定分區編號來均勻分布數據的一種分區類型，因為通過在I/O設備上進行散列分區，使得這些分區大小一致。

List分區（列表分區）：

當你需要明確地控制如何將行映射到分區時，就使用列表分區方法。與范圍分區和散列分區所不同，列表分區不支持多列分區。如果要將表按列分區，那么分區鍵就只能由表的一個單獨的列組成，然而可以用范圍分區或散列分區方法進行分區的所有的列,都可以用列表分區方法進行分區。

范圍-散列分區（復合分區）：

有時候我們需要根據范圍分區后，每個分區內的數據再散列地分布在幾個表空間中，這樣我們就要使用復合分區。復合分區是先使用范圍分區，然后在每個分區內再使用散列分區的一種分區方法（注意：先一定要進行范圍分區）

范圍-列表分區（復合分區）：

范圍和列表技術的組合，首先對表進行范圍分區，然后用列表技術對每個范圍分區再次分區。與組合范圍-散列分區不同的是，每個子分區的所有內容表示數據的邏輯子集，由適當的范圍和列表分區設置來描述。（注意：先一定要進行范圍分區）

#p#

四、分區表操作

--Partitioning 是否為true

select * from v$option s order by s.PARAMETER desc

--創建表空間

CREATE TABLESPACE "PARTION_03"   
LOGGING   
DATAFILE 'D:\ORACLE\ORADATA\JZHUA\PARTION_03.dbf' SIZE 50M   
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

--刪除表空間

drop tablespace partion_01

--范圍分區技術

create table Partition_Test   
(   
PID number not null,   
PITEM varchar2(200),   
PDATA date not null   
)   
partition by range(PID)   
(   
partition part_01 values less than(50000) tablespace dinya_space01,   
partition part_02 values less than(100000) tablespace dinya_space02,   
partition part_03 values less than(maxvalue) tablespace dinya_space03   
)   
create table Partition_TTest   
(   
PID number not null,   
PITEM varchar2(200),   
PDATA date not null   
)   
partition by range(PDATA)   
(   
partition part_t01 values less than(to_date('2004-01-01','yyyy-mm-dd')) tablespace dinya_space01,   
partition part_t02 values less than(to_date('2008-01-01','yyyy-mm-dd')) tablespace dinya_space02,   
partition part_t03 values less than(maxvalue) tablespace dinya_space03   
)   
insert into Partition_Test(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h   
select * from Partition_Test partition(part_01) t where t.pid = '1961'

--hash 分區技術

create table Partition_HashTest   
(   
PID number not null,   
PITEM varchar2(200),   
PDATA date not null   
)   
partition by hash(PID)   
(   
partition part_h01 tablespace dinya_space01,   
partition part_h02 tablespace dinya_space02,   
partition part_h03 tablespace dinya_space03   
)   
insert into Partition_HashTest(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h   
select * from Partition_HashTest partition(part_h03) t where t.pid = '1961'

--復合分區技術

create table Partition_FHTest   
(   
PID number not null,   
PITEM varchar2(200),   
PDATA date not null   
)   
partition by range(PDATA) subpartition by hash(PID) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)   
(   
partition part_fh01 values less than(to_date('2004-01-01','yyyy-mm-dd')) tablespace dinya_space01,   
partition part_fh02 values less than(to_date('2008-01-01','yyyy-mm-dd')) tablespace dinya_space02,   
partition part_fh03 values less than(maxvalue) tablespace dinya_space03   
)   
insert into Partition_FHTest(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h   
select * from Partition_FHTest partition(part_fh02) t where t.pid = '1961'   
select * from Partition_FHTest partition(part_fh03) t

--速度比較

select * from st_handle h where h.rectime > to_date('2008-01-01','yyyy-mm-dd');   
select * from Partition_FHTest partition(part_fh03) t where t.pdata > to_date('2008-01-01','yyyy-mm-dd');

--分區表操作

--增加一個分區

alter table Partition_Test add partition part_05 values less than (10020) tablespace dinya_space03

--查詢分區數據

select * from Partition_FHTest partition(part_fh02) t

--修改分區里的數據

update Partition_FHTest partition(part_fh02) t set t.PITEM = 'JZHUA' where t.pid = '1961'

--刪除分區里的數據

delete from Partition_FHTest partition(part_fh02) t where t.pid = '1961'

--合并分區

create table Partition_HB   
(   
PID number not null,   
PITEM varchar2(200),   
PDATA date not null   
)   
partition by range(PID)   
(   
partition part_01 values less than(50000) tablespace dinya_space01,   
partition part_02 values less than(100000) tablespace dinya_space02,   
partition part_03 values less than(maxvalue) tablespace dinya_space03   
)   
insert into Partition_HB(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h   
select * from Partition_HB partition(part_03) t where t.pid = '100001'   
alter table Partition_HB merge partitions part_01,part_02 into partition part_02;

--拆分分區

-- spilt partition 分區名 at(這里是一個臨界區，比如：50000就是說小于50000的放在part_01，而大于50000的放在part_02中)   
alter table Partition_HB split Partition part_02 at (50000) into (Partition part_01 tablespace dinya_space01, Partition part_02 tablespace dinya_space02);

--更改分區名

alter table Partition_HB rename Partition part_01_test to part_02;

#p#

五、索引分區表操作

分區表和一般表一樣可以建立索引，分區表可以創建局部索引和全局索引。當分區中出現許多事務并且要保證所有分區中的數據記錄的唯一性時采用全局索引。全局索引建立時 global 子句允許指定索引的范圍值，這個范圍值為索引字段的范圍值。其實理論上有3中分區索引。

Global索引（全局索引）：

對于 global 索引，可以選擇是否分區，而且索引的分區可以不與表分區相對應。當對分區進行維護操作時，通常會導致全局索引的 Invalid，必須在執行完操作后 Rebuild。Oracle9i 提供了 Update Global Indexes 語句，可以在進行分區維護的同時重建全局索引。

1：索引信息的存放位置與父表的Partition（分區）信息完全不相干。甚至父表是不是分區表都無所謂的。

create index dinya_idx_t on dinya_test(item_id) global partition by range(item_id) (   
partition idx_1 values less than (1000) tablespace dinya_space01,   
partition idx_2 values less than (10000) tablespace dinya_space02,   
partition idx_3 values less than (maxvalue) tablespace dinya_space03   
);

2：但是在這種情況下，如果父表是分區表，要刪除父表的一個分區都必須要更新Global Index ,否則索引信息不正確

ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes

Local索引（局部索引）：

對于 local 索引，每一個表分區對應一個索引分區（就是說一個分區表一個字段只可以建一個局部索引），當表的分區發生變化時，索引的維護由 Oracle 自動進行；

1：索引信息的存放位置依賴于父表的Partition（分區）信息，換句話說創建這樣的索引必須保證父表是Partition（分區），索引信息存放在父表的分區所在的表空間。

2：但是僅可以創建在父表為HashTable或者composite分區表的。

3：僅可以創建在父表為HashTable或者composite分區表的。并且指定的分區數目要與父表的分區數目要一致。

create index dinya_idx_t on dinya_test(item_id) local (   
partition idx_1 tablespace dinya_space01,   
partition idx_2 tablespace dinya_space02,   
partition idx_3 tablespace dinya_space03   
);

不指定索引分區名直接對整個表建立索引

create index dinya_idx_t on dinya_test(item_id);

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

#p#

ORACLE 為構建數據倉庫提供了4種類型的分區方法：Range Partition ,Hash Partition ,List Partition,Composite Partition.

下面我分別對這四種分區方法的概念，他們的使用場景，以及各種分區方法做一個性能比較。

一：概念

1：Range Partitioning

這是最常用的一種分區方法，基于COLUMN的值范圍做分區，最常見的是基于時間字段的數據的范圍的分區，比如：對于SALE表，可以對銷售時間按照月份做一個Range Partitioning。這種分區在數據倉庫里用的比較多，以下是

CREATE STATMENT   
CREATE TABLE sales_range   
(salesman_id NUMBER(5),   
salesman_name VARCHAR2(30),   
sales_amount NUMBER(10),   
sales_date DATE)   
COMPRESS   
PARTITION BY RANGE(sales_date)   
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),   
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),   
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),   
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));

對于COMPRESS關鍵字的理解，將在后續的壓縮分區講到

2;Hash Partitioning

Hash Partitioning映射數據到基于HASH算法的分區上，HASH算法將應用你指定的分區關鍵字，平均的分那些在Partitions中的行。給每一個分區近似相同的大小，要保證數據能平均分配，分區數一般是2N。比如說，需要insert sales_hash 一條數據，ORACLE會通過HASH算法處理salesman_id，然后找到對于的分區表進行insert。Hash Partitioning 是為跨越設備的分布式數據提供了一種理想的方法，HASH算法也很容易轉化成RANGE分區方法，特別是當被分區的數據不是歷史數據時。

CREATE TABLE sales_hash   
(salesman_id NUMBER(5),   
salesman_name VARCHAR2(30),   
sales_amount NUMBER(10),   
week_no NUMBER(2))   
PARTITION BY HASH(salesman_id)   
PARTITIONS 4;

3：List Partitioning

List Partitioning能夠讓你明確的控制有多少行被分區，你能對要分區的COLUMN上明確的指定按照那些具體的值來分區，這種方式在Range和Hash方式是做不到的。這種方式的優點是，你能組織和分組那些沒有順序和沒有關系的數據集。下面是通過銷售地區做一個List分區表。

CREATE TABLE sales_list   
(salesman_id NUMBER(5),   
salesman_name VARCHAR2(30),   
sales_state VARCHAR2(20),   
sales_amount NUMBER(10),   
sales_date DATE)   
PARTITION BY LIST(sales_state)   
(PARTITION sales_west VALUES('California', 'Hawaii') COMPRESS,   
PARTITION sales_east VALUES('New York', 'Virginia', 'Florida'),   
PARTITION sales_central VALUES('Texas', 'Illinois'));

4：Composite Partitioning

Composite Partitioning 是把Range ,Hash ,List 分區方式組合起來的分區方式。

比如Composite Range-Hash Partitioning和Composite Range-List Partitioning：

CREATE TABLE sales_range_hash(   
s_productid NUMBER,   
s_saledate DATE,   
s_custid NUMBER,   
s_totalprice NUMBER)   
PARTITION BY RANGE (s_saledate)   
SUBPARTITION BY HASH (s_productid) SUBPARTITIONS 8   
(PARTITION sal99q1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),   
PARTITION sal99q2 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),   
PARTITION sal99q3 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),   
PARTITION sal99q4 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));

另外你還可以用subpartition template的方式指定：

CREATE TABLE sales_range_hash(   
s_productid NUMBER,   
s_saledate DATE,   
s_custid NUMBER,   
s_totalprice NUMBER)   
PARTITION BY RANGE (s_saledate)   
SUBPARTITION BY HASH (s_productid)   
SUBPARTITION TEMPLATE(   
SUBPARTITION sp1 TABLESPACE tbs1,   
SUBPARTITION sp2 TABLESPACE tbs2,   
SUBPARTITION sp3 TABLESPACE tbs3,   
SUBPARTITION sp4 TABLESPACE tbs4,   
SUBPARTITION sp5 TABLESPACE tbs5,   
SUBPARTITION sp6 TABLESPACE tbs6,   
SUBPARTITION sp7 TABLESPACE tbs7,   
SUBPARTITION sp8 TABLESPACE tbs8)   
(PARTITION sal99q1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),   
PARTITION sal99q2 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),   
PARTITION sal99q3 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),   
PARTITION sal99q4 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));

這樣，沒有子分區通過的HASH分區將會統一到不同的表空間。

#p#

二：使用各種分區方法的場景

1：什么時候用Range Partition

Range Partition是一種方便的方法分區歷史的數據，經常在DATE COLMUN通過時間間隔組織數據。比如說：你要查詢2009年8月的數據，查詢將直接找到2009年8月的分區，避免了大量不必要的數據掃描。

在處理周期性的load新數據和purge老數據的時候，Range Partition也是一個理想的選擇。

應用場景：

a)有一個大表需要通過時間字段頻繁的訪問，通過這個時間字段做RANG PARTITION 有利于做分區裁剪。

b）如果你不能對一個大表在指定的時間內做備份或RESTORE,你可以通過RANGE把他們分成小的logic片來做。

2：什么時候用HASH Partition

HASH Partition不是一個很好的管理歷史的方法。

應用場景

a）增加大表的可用性。

b）避免各個分區之間查找數據，并且各個分區可以放在不同的設備上，達到***的I0吞吐量。也可以用STORE IN 子句分配每個分區到不同的表空間。

3：什么時候用LIST Partition

如果你想映射數據到離散的值的時候，LIST Partition是個比較好的選擇。

4：什么時候用Composite Range-Hash Partitioning

這是Range和Hash的組合使用，先對表用RANGE分，然后對每個RANGE再做HASH分區。

由于做了RANGE后的子分區是沒有規律的，如果在數據倉庫設計時候，通過查詢需求覺得有必要再細分，可以考慮使用。ORACLE會把子分區又分成不同的SEGMENT。

原文鏈接：http://www.cnblogs.com/tracy/archive/2011/05/31/2064027.html

【編輯推薦】

MySQL數據庫分區管理細節決定成敗
***SQL Server數據庫表分區的實現

責任編輯：艾婧來源：博客園

Oracle 分區