當前位置:我的異常網? 編程???Oracle分區表詳解

Oracle分區表詳解

www.myexceptions.net??網友分享于：2013-10-28??瀏覽：25次

Oracle分區表詳解 .

一、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:ORACLEORADATAJZHUAPARTION_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中分區索引。

索引分區是在您建立了表分區后，要建索引就必須是建立索引分區。分2大類：一類是把索引信息建立在各個分區上，這叫局部索引分區(或叫本地索引分區)。另一類是把索引集中起來，叫全局索引。

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

建立方法：

create index ind_2 on sales (amount_sold)

global partition by range (amount_sold)

(partition d1 ,

partition d2);

因為全局索引的首個字段必須是range字段，所以就無所謂前綴和非前綴了，都是前綴。

經過試驗，我覺得建立全局索引的速度要略遜于局部前綴索引。

也可能是我的能力問題，現在覺得建立分區還不然不建立索引。如果大家能給我解惑

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);

建立方法：

create index ind_1 on dept (deptno)

local

(partition d1 ,

partition d2);

(1)局部前綴索引分區和局部非前綴分區。如果您擬建立的索引的首個字段，和進行分區時的range列一樣，那就是局部前綴索引分區。

優點是：理論上(我認為的)，比方說您以年代為range分區，2007年一個分區、2008年一個分區，然后您又在這個時間列上建立了局部前綴索引分區，那么ORACLE就會直接利用這個區上的索引僅進行這個分區上的搜索，所以效率會很高。

在我建立的2000萬的表中進行查詢，實踐是，這個局部前綴復合索引的花銷cost是5，而沒有分區前是4。當然這也無所謂了。又進行了其他幾個查詢，其cost都相差無幾。

(2)局部非前綴索引。如果您建立索引的列的首個字段不是range列，那么就叫局部非前綴索引。

優點是：如果您查一個電話號碼，它在每年都會出現，當您要count匯總時，這種索引就會同時把這幾個分區進行并行處理查詢，速度理論上要快。

但我的試驗比較令我失望：我建了一個2000萬的無分區的表，然后把這個表又復制了一遍，進行了6個分區。但結果在對某列進行查詢統計時，如果在一個分區，兩者速度相差不大，分區的查詢速度是：0.25m，無分區的查詢速度是：0.065m。但在我期望的跨區統計時，分區的第一次統計時間是：61.875m，第二次是：10m；而無分區的表僅為：3.703m。

文章評論

總結

以上是生活随笔為你收集整理的oracle分区表编程,Oracle分区表详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。