簡單說說Oracle分區(qū)
一、簡介
ORACLE的分區(qū)是一種處理超大型表、索引等的技術(shù)。分區(qū)是一種“分而治之”的技術(shù)，通過將大表和索引分成可以管理的小塊，從而避免了對每個(gè)表作為一個(gè)大的、單獨(dú)的對象進(jìn)行管理，為大量數(shù)據(jù)提供了可伸縮的性能。分區(qū)通過將操作分配給更小的存儲單元，減少了需要進(jìn)行管理操作的時(shí)間，并通過增強(qiáng)的并行處理提高了性能，通過屏蔽故障數(shù)據(jù)的分區(qū)，還增加了可用性。
二、優(yōu)缺點(diǎn)
優(yōu)點(diǎn)：
增強(qiáng)可用性：如果表的某個(gè)分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用；
維護(hù)方便：如果表的某個(gè)分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可；
均衡I/O：可以把不同的分區(qū)映射到磁盤以平衡I/O，改善整個(gè)系統(tǒng)性能；
改善查詢性能：對分區(qū)對象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度。
缺點(diǎn)：
分區(qū)表相關(guān)：已經(jīng)存在的表沒有方法可以直接轉(zhuǎn)化為分區(qū)表。不過 Oracle 提供了在線重定義表的功能。
三、分區(qū)方法
范圍分區(qū)：
范圍分區(qū)就是對數(shù)據(jù)表中的某個(gè)值的范圍進(jìn)行分區(qū)，根據(jù)某個(gè)值的范圍，決定將該數(shù)據(jù)存儲在哪個(gè)分區(qū)上。如根據(jù)序號分區(qū)，根據(jù)業(yè)務(wù)記錄的創(chuàng)建日期進(jìn)行分區(qū)等。
Hash分區(qū)（散列分區(qū)）：
散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型，因?yàn)橥ㄟ^在I/O設(shè)備上進(jìn)行散列分區(qū)，使得這些分區(qū)大小一致。
List分區(qū)（列表分區(qū)）：
當(dāng)你需要明確地控制如何將行映射到分區(qū)時(shí)，就使用列表分區(qū)方法。與范圍分區(qū)和散列分區(qū)所不同，列表分區(qū)不支持多列分區(qū)。如果要將表按列分區(qū)，那么分區(qū)鍵就只能由表的一個(gè)單獨(dú)的列組成，然而可以用范圍分區(qū)或散列分區(qū)方法進(jìn)行分區(qū)的所有的列,都可以用列表分區(qū)方法進(jìn)行分區(qū)。
范圍-散列分區(qū)（復(fù)合分區(qū)）：
有時(shí)候我們需要根據(jù)范圍分區(qū)后，每個(gè)分區(qū)內(nèi)的數(shù)據(jù)再散列地分布在幾個(gè)表空間中，這樣我們就要使用復(fù)合分區(qū)。復(fù)合分區(qū)是先使用范圍分區(qū)，然后在每個(gè)分區(qū)內(nèi)再使用散列分區(qū)的一種分區(qū)方法（注意：先一定要進(jìn)行范圍分區(qū)）
范圍-列表分區(qū)（復(fù)合分區(qū)）：
范圍和列表技術(shù)的組合，首先對表進(jìn)行范圍分區(qū)，然后用列表技術(shù)對每個(gè)范圍分區(qū)再次分區(qū)。與組合范圍-散列分區(qū)不同的是，每個(gè)子分區(qū)的所有內(nèi)容表示數(shù)據(jù)的邏輯子集，由適當(dāng)?shù)姆秶土斜矸謪^(qū)設(shè)置來描述。（注意：先一定要進(jìn)行范圍分區(qū)）
#p#
四、分區(qū)表操作
--Partitioning 是否為true
- select * from v$option s order by s.PARAMETER desc
--創(chuàng)建表空間
- 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
--范圍 分區(qū)技術(shù)
- 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 分區(qū)技術(shù)
- 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'
--復(fù)合分區(qū)技術(shù)
- 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');
--分區(qū)表操作
--增加一個(gè)分區(qū)
- alter table Partition_Test add partition part_05 values less than (10020) tablespace dinya_space03
--查詢分區(qū)數(shù)據(jù)
- select * from Partition_FHTest partition(part_fh02) t
--修改分區(qū)里的數(shù)據(jù)
- update Partition_FHTest partition(part_fh02) t set t.PITEM = 'JZHUA' where t.pid = '1961'
--刪除分區(qū)里的數(shù)據(jù)
- delete from Partition_FHTest partition(part_fh02) t where t.pid = '1961'
--合并分區(qū)
- 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;
--拆分分區(qū)
- -- spilt partition 分區(qū)名 at(這里是一個(gè)臨界區(qū)，比如：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);
--更改分區(qū)名
- alter table Partition_HB rename Partition part_01_test to part_02;
#p#
五、索引分區(qū)表操作
分區(qū)表和一般表一樣可以建立索引，分區(qū)表可以創(chuàng)建局部索引和全局索引。當(dāng)分區(qū)中出現(xiàn)許多事務(wù)并且要保證所有分區(qū)中的數(shù)據(jù)記錄的唯一性時(shí)采用全局索引。全局索引建立時(shí) global 子句允許指定索引的范圍值，這個(gè)范圍值為索引字段的范圍值。其實(shí)理論上有3中分區(qū)索引。
Global索引（全局索引）：
對于 global 索引，可以選擇是否分區(qū)，而且索引的分區(qū)可以不與表分區(qū)相對應(yīng)。當(dāng)對分區(qū)進(jìn)行維護(hù)操作時(shí)，通常會導(dǎo)致全局索引的 Invalid，必須在執(zhí)行完操作后 Rebuild。Oracle9i 提供了 Update Global Indexes 語句，可以在進(jìn)行分區(qū)維護(hù)的同時(shí)重建全局索引。
1：索引信息的存放位置與父表的Partition（分區(qū)）信息完全不相干。甚至父表是不是分區(qū)表都無所謂的。
- 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：但是在這種情況下，如果父表是分區(qū)表，要刪除父表的一個(gè)分區(qū)都必須要更新Global Index ,否則索引信息不正確
- ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes
Local索引（局部索引）：
對于 local 索引，每一個(gè)表分區(qū)對應(yīng)一個(gè)索引分區(qū)（就是說一個(gè)分區(qū)表一個(gè)字段只可以建一個(gè)局部索引），當(dāng)表的分區(qū)發(fā)生變化時(shí)，索引的維護(hù)由 Oracle 自動進(jìn)行；
1：索引信息的存放位置依賴于父表的Partition（分區(qū)）信息，換句話說創(chuàng)建這樣的索引必須保證父表是Partition（分區(qū)），索引信息存放在父表的分區(qū)所在的表空間。
2：但是僅可以創(chuàng)建在父表為HashTable或者composite分區(qū)表的。
3：僅可以創(chuàng)建在父表為HashTable或者composite分區(qū)表的。并且指定的分區(qū)數(shù)目要與父表的分區(qū)數(shù)目要一致。
- 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
- );
不指定索引分區(qū)名直接對整個(gè)表建立索引
- create index dinya_idx_t on dinya_test(item_id);
---------------------------------------
#p#
ORACLE 為構(gòu)建數(shù)據(jù)倉庫提供了4種類型的分區(qū)方法：Range Partition ,Hash Partition ,List Partition,Composite Partition.
下面我分別對這四種分區(qū)方法的概念，他們的使用場景，以及各種分區(qū)方法做一個(gè)性能比較。
一：概念
1：Range Partitioning
這是最常用的一種分區(qū)方法，基于COLUMN的值范圍做分區(qū)，最常見的是基于時(shí)間字段的數(shù)據(jù)的范圍的分區(qū)，比如：對于SALE表，可以對銷售時(shí)間按照月份做一個(gè)Range Partitioning。這種分區(qū)在數(shù)據(jù)倉庫里用的比較多，以下是
- 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關(guān)鍵字的理解，將在后續(xù)的壓縮分區(qū)講到
2;Hash Partitioning
Hash Partitioning映射數(shù)據(jù)到基于HASH算法的分區(qū)上，HASH算法將應(yīng)用你指定的分區(qū)關(guān)鍵字，平均的分那些在Partitions中的行。給每一個(gè)分區(qū)近似相同的大小，要保證數(shù)據(jù)能平均分配，分區(qū)數(shù)一般是2N。比如說，需要insert sales_hash 一條數(shù)據(jù)，ORACLE會通過HASH算法處理salesman_id，然后找到對于的分區(qū)表進(jìn)行insert。Hash Partitioning 是為跨越設(shè)備的分布式數(shù)據(jù)提供了一種理想的方法，HASH算法也很容易轉(zhuǎn)化成RANGE分區(qū)方法，特別是當(dāng)被分區(qū)的數(shù)據(jù)不是歷史數(shù)據(jù)時(shí)。
- 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能夠讓你明確的控制有多少行被分區(qū)，你能對要分區(qū)的COLUMN上明確的指定按照那些具體的值來分區(qū)，這種方式在Range和Hash方式是做不到的。這種方式的優(yōu)點(diǎn)是，你能組織和分組那些沒有順序和沒有關(guān)系的數(shù)據(jù)集。下面是通過銷售地區(qū)做一個(gè)List分區(qū)表。
- 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 分區(qū)方式組合起來的分區(qū)方式。
比如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')));
這樣，沒有子分區(qū)通過的HASH分區(qū)將會統(tǒng)一到不同的表空間。
#p#
二：使用各種分區(qū)方法的場景
1：什么時(shí)候用Range Partition
Range Partition是一種方便的方法分區(qū)歷史的數(shù)據(jù)，經(jīng)常在DATE COLMUN通過時(shí)間間隔組織數(shù)據(jù)。比如說：你要查詢2009年8月的數(shù)據(jù)，查詢將直接找到2009年8月的分區(qū)，避免了大量不必要的數(shù)據(jù)掃描。
在處理周期性的load新數(shù)據(jù)和purge老數(shù)據(jù)的時(shí)候，Range Partition也是一個(gè)理想的選擇。
應(yīng)用場景：
a)有一個(gè)大表需要通過時(shí)間字段頻繁的訪問，通過這個(gè)時(shí)間字段做RANG PARTITION 有利于做分區(qū)裁剪。
b）如果你不能對一個(gè)大表在指定的時(shí)間內(nèi)做備份或RESTORE,你可以通過RANGE把他們分成小的logic片來做。
2：什么時(shí)候用HASH Partition
HASH Partition不是一個(gè)很好的管理歷史的方法。
應(yīng)用場景
a）增加大表的可用性。
b）避免各個(gè)分區(qū)之間查找數(shù)據(jù)，并且各個(gè)分區(qū)可以放在不同的設(shè)備上，達(dá)到***的I0吞吐量。也可以用STORE IN 子句分配每個(gè)分區(qū)到不同的表空間。
3：什么時(shí)候用LIST Partition
如果你想映射數(shù)據(jù)到離散的值的時(shí)候，LIST Partition是個(gè)比較好的選擇。
4：什么時(shí)候用Composite Range-Hash Partitioning
這是Range和Hash的組合使用，先對表用RANGE分，然后對每個(gè)RANGE再做HASH分區(qū)。
由于做了RANGE后的子分區(qū)是沒有規(guī)律的，如果在數(shù)據(jù)倉庫設(shè)計(jì)時(shí)候，通過查詢需求覺得有必要再細(xì)分，可以考慮使用。ORACLE會把子分區(qū)又分成不同的SEGMENT。
原文鏈接：http://www.cnblogs.com/tracy/archive/2011/05/31/2064027.html
【編輯推薦】
