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中分割槽索引。

? 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);
-------------------------------------------------------分割槽2-----------------------------------------------------------------------------

　最近在做一個客戶關係管理系統，專案做的到不是非常成功，可還是學到了不少的知識，由於資料量很大，沒有專門的oracle資料庫人員支援，對資料庫最佳化管理等也只有我這個約懂一點的人上了。在對資料庫最佳化上有一點點心得寫出來希望能同大家一起學習和交流。

　　資料庫大表的最佳化：採用蔟表（clustered tables）及蔟索引（Clustered Index）

　　蔟表和蔟索引是oracle所提供的一種技術，其基本思想是將幾張具有相同資料項、並且經常性一起使用的表透過共享資料塊（data block）的模式存放在一起。各表間的共同欄位作為蔟鍵值（cluster key），資料庫在訪問資料時，首先找到蔟鍵值，以此同時獲得若干張表的相關資料。蔟表所能帶來的好處是可以減少I/O和減少儲存空間，其中我更看重前者。採用表分割槽（partition）

　　表分割槽技術是在超大型資料庫(VLDB)中將大表及其索引透過分割槽（patition）的形式分割為若干較小、可管理的小塊，並且每一分割槽可進一步劃分為更小的子分割槽（sub partition）。而這種分割槽對於應用來說是透明的。透過對錶進行分割槽，可以獲得以下的好處：

　　1）減少資料損壞的可能性。

　　2）各分割槽可以獨立備份和恢復，增強了資料庫的可管理性。

　　3）可以控制分割槽在硬碟上的分佈，以均衡IO，改善了資料庫的效能。

　　蔟表與表分割槽技術的側重點各有不同，前者側重於改進關聯表間查詢的效率，而表分割槽側重於大表的可管理性及區域性查詢的效能。而這兩項對於我的系統來說都是極為重要。由於本人技術限制，目前尚不確定兩者是否可以同時實現，有那位在這方面有經驗的給點指導將不勝感激。

　　在兩者無法同時實現的情況下，應依照需實現的功能有所取捨。綜合兩種模式的優缺點，我認為採用表分割槽技術較為適用於我們的應用。

　　Oracle的表分割槽有以下幾種型別：

　　1）範圍分割槽：將表按某一欄位或若干個欄位的取值範圍分割槽。

　　2）hash分割槽：將表按某一欄位的值均勻地分佈到若干個指定的分割槽。

　　3）複合分割槽：結合了前面兩種分割槽型別的優點，首先透過值範圍將表進行分割槽，然後以hash模式將資料進一步均勻分配至物理儲存位置。

　　綜合考慮各項因素，以第三種型別最為優越。（本人實在技術有限僅採用了第1種範圍分割槽，因為比較簡單，便於管理）

　　最佳化的具體步驟：

　　1．確定需要最佳化分割槽的表：

　　經過對系統資料庫表結構和欄位，應用程式的分析，現在確定那些大表需要進行分割槽：

　　如帳戶交易明細表acct_detail.

　　2．確定表分割槽的方法和分割槽鍵：

　　分割槽型別：採用範圍分割槽。

　　分 區 鍵：

　　按trans_date（交易時間）欄位進行範圍分割槽.

　　3．確定分割槽鍵的分割槽範圍，及打算分多少分割槽：

　　如：帳戶交易明細表acct_detail.

　　根據欄位（trans_date）分成一下分割槽：

　　1）．分割槽1：09/01/2003

　　2）．分割槽2：10/01/2003

　　3）．分割槽3：11/01/2003

　　4）．分割槽4：12/01/2003

　　5）．分割槽5：01/01/2004

　　6）．分割槽6：02/01/2004

　　該表明顯需要在以後增加分割槽。

　　4．建立分割槽表空間和分割槽索引空間

　　1)．建立表的各個分割槽的表空間：

　　1．分割槽1：crm_detail_200309

　　CREATE TABLESPACE crm_detail_200309 DATAFILE

　　‘/u1/oradata/orcl/crm_detail_20030901.dbf’

　　SIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM size 16M;

　　其它月份以後同以上（我在此採用oracle的表空間本地管理的方法）。

　　2）. 建立分割槽索引表空間

　　1．分割槽1：index_detail_200309

　　CREATE TABLESPACE index_detail_200309 DATAFILE

　　‘/u3/oradata/orcl/index_detail_20030901.dbf’

　　SIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM size 16M;

　　5．建立基於分割槽的表：

　　create table table name

　　(

　　........

　　enable row movment --此語句是能修改行分割槽鍵值，也就是如不新增該 句不能修改記錄的分割槽鍵值，不能使記錄分割槽遷移

　　PARTITION BY RANGE (TRANS_DATE)

　　(

　　PARTITION crm_detail_200309 VALUES LESS THAN

　　(TO_DATE (‘09/01/2003’,’mm/dd/yyyy’

　　TABLESPACE crm_detail_200309,

　　其他分割槽.....

　　;

　　6．建立基於分割槽的索引：

　　create index index_name on table_name (分割槽鍵+…)

　　global --這裡是全域性分割槽索引，也可以建本地索引

　　PARTITION BY RANGE (TRANS_DATE)

　　(

　　PARTITION index_detail_200309 VALUES LESS THAN

　　(TO_DATE ('09/01/2003','mm/dd/yyyy' )

　　TABLESPACE index_detail_200309,

　　其他索引分割槽...

　　;

　　對錶的分割槽就這樣完成了，第一次主要確定表分割槽的分割槽策約是最重要的，可我覺得對錶分割槽難在以後對錶分割槽的管理上面，因為隨著資料量的增加，表分割槽必然存在刪除，擴容，增加等。在這些過程中還牽涉到全域性等索引，因為對分割槽表進行ddl操作為破壞全域性索引，故全域性索引必須在ddl後要重 rebuild.
---------------------------------------------------------------------分割槽3-----------------------------------------------------------------

Oracle的普通表沒有辦法透過修改屬性的方式直接轉化為分割槽表，必須透過重建的方式進行轉變，下面介紹三種效率比較高的方法，並說明它們各自的特點。

方法一：利用原表重建分割槽表。

步驟：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已建立。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已建立6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE(''2004-7-1'', ''YYYY-MM-DD'')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE(''2005-1-1'', ''YYYY-MM-DD'')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE(''2005-7-1'', ''YYYY-MM-DD'')),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE))
6 AS SELECT ID, TIME FROM T;

表已建立。

SQL> RENAME T TO T_OLD;

表已重新命名。

SQL> RENAME T_NEW TO T;

表已重新命名。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
6264

SQL> SELECT COUNT(*) FROM T PARTITION (P1);

COUNT(*)
----------
0

SQL> SELECT COUNT(*) FROM T PARTITION (P2);

COUNT(*)
----------
6246

SQL> SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)
----------
18

優點：方法簡單易用，由於採用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成後資料已經在分佈到各個分割槽中了。

不足：對於資料的一致性方面還需要額外的考慮。由於幾乎沒有辦法透過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句後對資料進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。

適用於修改不頻繁的表，在閒時進行操作，表的資料量不宜太大。

方法二：使用交換分割槽的方法。

步驟：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已建立。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已建立6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE(''2005-7-1'', ''YYYY-MM-DD'')),
3 PARTITION P2 VALUES LESS THAN (MAXVALUE));

表已建立。

SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

表已更改。

SQL> RENAME T TO T_OLD;

表已重新命名。

SQL> RENAME T_NEW TO T;

表已重新命名。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
6264

優點：只是對資料字典中分割槽和表的定義進行了修改，沒有資料的修改或複製，效率最高。如果對資料在分割槽中的分佈沒有進一步要求的話，實現比較簡單。在執行完RENAME操作後，可以檢查T_OLD中是否存在資料，如果存在的話，直接將這些資料插入到T中，可以保證對T插入的操作不會丟失。

不足：仍然存在一致性問題，交換分割槽之後RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到資料。如果要求資料分佈到多個分割槽中，則需要進行分割槽的SPLIT操作，會增加操作的複雜度，效率也會降低。

適用於包含大資料量的錶轉到分割槽表中的一個分割槽的操作。應儘量在閒時進行操作。

方法三：Oracle9i以上版本，利用線上重定義功能

步驟：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已建立。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已建立6264行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, ''T'', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 過程已成功完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE(''2004-7-1'', ''YYYY-MM-DD'')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE(''2005-1-1'', ''YYYY-MM-DD'')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE(''2005-7-1'', ''YYYY-MM-DD'')),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已建立。

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, ''T'', ''T_NEW'', -
> ''ID ID, TIME TIME'', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(''YANGTK'', ''T'', ''T_NEW'');

PL/SQL 過程已成功完成。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
6264

SQL> SELECT COUNT(*) FROM T PARTITION (P2);

COUNT(*)
----------
6246

SQL> SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)
----------
18

優點：保證資料的一致性，在大部分時間內，表T都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。

不足：實現上比上面兩種略顯複雜。

適用於各種情況。

這裡只給出了線上重定義表的一個最簡單的例子，詳細的描述和例子可以參考下面兩篇文章。

Oracle的線上重定義表功能：http://blog.itpub.net/post/468/12855

Oracle的線上重定義表功能（二）：http://blog.itpub.net/post/468/12962

二、索引分割槽的概念 及建索引方法

索引分割槽是在您建立了表分割槽後，要建索引就必須是建立索引分割槽。分2大類：一類是把索引資訊建立在各個分割槽上，這叫區域性索引分割槽（或叫本地索引分割槽）。另一類是把索引集中起來，叫全域性索引。

1、區域性索引又分2類。
建立方法：

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。

2、全域性索引。
建立方法：

create index ind_2 on sales (amount_sold)
global partition by range (amount_sold)
(partition d1 ,
partition d2);
因為全域性索引的首個欄位必須是range欄位，所以就無所謂字首和非字首了，都是字首。
經過試驗，我覺得建立全域性索引的速度要略遜於區域性字首索引。

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