[DB][Oracle][PARTITION]大資料量分區技術

[Oracle][PARTITION]大資料量分區技術

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

二、Oracle分區優缺點
優點：
增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；
維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；
均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能；
改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。
缺點：
分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了線上重定義表的功能。
三、Oracle分區方法
定界分割：
定界分割就是對資料表中的某個值的範圍進行分區，根據某個值的範圍，決定將該資料存放區在哪個分區上。如根據序號分區，根據業務記錄的建立日期進行分區等。
Hash分區（散列分區）：
散列分區為通過指定分區編號來均勻分布資料的一種分區類型，因為通過在I/O裝置上進行散列分區，使得這些分區大小一致。
List分區（列表分區）：
當你需要明確地控制如何將行映射到分區時，就使用列表分區方法。與定界分割和散列分區所不同，列表分區不支援多列分區。如果要將表按列分區，那麼分區鍵就只能由表的一個單獨的列組成，然而可以用定界分割或散列分區方法進行分區的所有的列,都可以用列表分區方法進行分區。

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

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

四、Oracle表分區表操作
--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;
五、Oracle索引分割區表操作
分區表和一般表一樣可以建立索引，分區表可以建立局部索引和全域索引。當分區中出現許多事務並且要保證所有分區中的資料記錄的唯一性時採用全域索引。全域索引建立時 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);