9.1.4 使用分割槽表，索引組織表，簇表，臨時表

9.1.4.1 分割槽表(partition table)

分割槽表是一種虛表，沒有對應的segment, 分割槽表的segment由該表的分割槽對應，
也就是說分割槽表具有多個不同的segment, 但是邏輯上講，分割槽表是一個完成的表，
具有表的所有特性，而所有的分割槽具有相同的邏輯特性。

引入分割槽表後，可以將不同分割槽放在不同的磁碟上，如果某個磁碟損壞，則只有該
損壞磁碟上的資料無法訪問，其他正常的都可以訪問。可以以分割槽為單位進行備份。
對於效能來說，Oracle能夠進行分割槽排除(partition pruning), 如果在c1欄位列上
做了分割槽，那麼對於where c1='ABC' 這樣的條件來說，oracle可以知道c1='ABC'分
布在哪個分割槽，從而直接訪問該分割槽，不需要對整個表進行掃描。

分割槽種類：
A. 範圍分割槽(Range Partition)
B. 雜湊分割槽(Hash Partition)
C. 列表分割槽(List Partition)
D. 範圍雜湊組合分割槽(Range-Hash Partition)
E. 範圍列表組合分割槽Range-List Partition)

對於普通表(也就是堆組織表)，上述分割槽形式都可以使用(甚至可以對某個分割槽指定
compress屬性)，只是分割槽依賴列不能是LOB及Long之類的資料型別，每個表的分割槽
或子分割槽總數不能超過 1024K-1個。

對於索引組織表(IOT),只能夠支援普通分割槽方式，不支援組合分割槽，常規表的限制
對於索引組織表同樣有效，此外還有一些其他的限制，比如要求索引組織表的分割槽
依賴列必須是主鍵才可以等等。這裡只討論普通表。

Oracle一般建議如果單個表超過2G最好對其進行分割槽。如果想對某個表分割槽，必須
在建立的時候就指定分割槽，當然我們可以將一個未分割槽的表修改為分割槽表，但是不
能直接修改，需要臨時表間接實現。

如何建立分割槽表 --

建立分割槽表的時候需要注意的有，row movement屬性，它用來控制是否允許修改
列值 (指分割槽列) 所造成的記錄移動至其他分割槽儲存，預設是disable的，Oracle允
許修改分割槽列，如果設定為enable row movement, 那麼Oracle允許分割槽列發生改變
後如果值屬於另外一個分割槽，記錄自動轉移到其他分割槽，且Oracle會自動維護索引。
但是相應的rowid也會不可避免的發生變化，影響比較大的是Logminer中的SQL_UNDO.

當row movement設定為disable時，如果記錄被更新到其他分割槽，則更新語句會報錯。
因為比較難控制是否分割槽列是否會有更新，所以一般我們在建立分割槽表的時候會設定
ENABLE ROW MOVEMENT .

A. 範圍分割槽(Range Partition)
根據某個值的範圍進行分割槽，典型的就是時間欄位。比如每半年的資料放在一個分割槽中。
範圍分割槽支援指定多個列作為依賴列，可以建立一個maxvalue分割槽，不在指定範圍的記錄
都會記錄到maxvalue所在分割槽。不過加了maxvalue之後，如果需要新加入分割槽，需要進行
split , 如果沒有使用maxvalue，可以直接add partition .

eg: create table t2 (id number, createdate date)
     partition by range (createdate)
      (
        partiton p1 values less than (to_date('2008-01-01','yyyy-mm-dd')) tablespace tbs01,
        partiton p2 values less than (to_date('2009-01-01','yyyy-mm-dd')) tablespace tbs02,
        partiton p3 values less than (to_date('2010-01-01','yyyy-mm-dd')) tablespace tbs03,
        partiton pmax values less than (maxvalue) tablespace tbs04
      ) ;

查詢各分割槽記錄：select * from test01 partition (p1);
設計的檢視有： user_part_tables 記錄分割槽表的資訊， user_tab_partitions 記錄表的分割槽資訊

B. 雜湊分割槽(Hash Partition)

對列的值運用hash函式，將得到的結果作為分割槽的區號。通常在表的欄位值不容易
劃分範圍的時候使用hash分割槽，比如對於主鍵列，就可以採用hash分割槽。hash分割槽
最大的特點在於在不同的分割槽裡平均分佈資料，在建立hash分割槽時，分割槽的個數應該
是2的冪，否則分割槽表的資料分佈會不平衡。hash分割槽可以支援多個依賴列。

具體為什麼需要是2的冪資料分佈才會平衡，可以參考簡單測試：
http://www.itpub.net/thread-1390083-1-1.html

Hash分割槽指定分割槽有兩種方式：
1), 直接指定分割槽名，分割槽所在表空間等資訊
2), 只指定分割槽數量，和可供使用的表空間

eg: create table t3 (id number, name varchar2(20))
     partition by hash(id)
      (
        partiton p1 tablespace tbs01,
        partiton p2 tablespace tbs02,
        partiton p3 tablespace tbs03,
        partiton p4 tablespace tbs04
      ) ;

eg: create table t3 (id number, name varchar2(20))
     partition by hash(id)
     partitions 4
     store in (ts01,ts02,ts03,ts04) ;

備註: 這裡分割槽數量和可供使用的表空間數量之間沒有直接對應關係。
分割槽數並不一定要等於表空間數。

可以測試一下，詳細參考：
http://space.itpub.net/35489/viewspace-684470

1)， HASH分割槽不存在split partition，只能add partition。資料在各個分割槽的分佈
不能人為控制，不能顯示的指定某一個分割槽進行分裂，但是加入分割槽後，從上面的例
子可以看出資料拆分分佈是有規律的。
例如： alter table test01 add partition p9 ;

2)，當我們確定合理的分割槽數量的之後，資料的分佈完全由分割槽表中的資料本身決定。
對於某些特定的資料來說，HASH分割槽後的效果可能並不好。資料的隨機性越大，資料的
樣本量越大，HASH分割槽後的效果越好，因為資料有可能更加平均的分散到每個bucket中。

3)，對於分割槽個數為m的HASH分割槽表來說，無論期間經歷了怎麼樣的過程（比如說先建
立n個分割槽的HASH分割槽表(n區數為m的分割槽表；）最後的資料分佈都是相近的(不完全一樣) 。

4)，對於HASH分割槽表，drop partition操作是不可以的。
alter table test01 drop partition p1;
ORA-14255: 未按範圍, 組合範圍或列表方法對錶進行分割槽

C. 列表分割槽(List Partition)

當分割槽的值為一些離散值的時候，該資料行就進入某個值指定的分割槽。它的
分割槽列只能有一個，而不像range或hash可以同時指定多個列作為分割槽依賴列，
不過它的單個分割槽對應值可以是多個。一旦插入的值不在分割槽範圍，則會報
錯，所以一般建議建立一個default分割槽儲存那些不在指定範圍的記錄，類
似range分割槽中的maxvalue分割槽。

eg :
   create table t4 (id number, createdate date, category varchar2(10))
     partition by list (category)
      (
        partiton p1 values ('01','02') tablespace tbs01,
        partiton p2 values ('03','04') tablespace tbs02,
        partiton p3 values ('05','06') tablespace tbs03,
        partiton p4 values ('default) tablespace tbs04
      ) ;

注意： 10g中組合分割槽有兩種：Range-hash, Range-list . 注意順序，根
分割槽只能是Range分割槽，子分割槽可以是hash分割槽或list分割槽。不過在11g中
分割槽組合方式還有 range-range, list-range, list-list, list-hash,
這就相當於除了hash外三種分割槽方式的笛卡爾形式都有了。

D. 範圍雜湊組合分割槽(Range-Hash Partition)

先按照某個欄位進行範圍分割槽，然後再對其他的列進行hash分割槽，得出的hash
分割槽叫做子分割槽，語法詳見oracle 文件.

需要指定的有：
column_list:分割槽依賴列(支援多個，中間以逗號分隔);
subpartition:子分割槽方式，有兩處：
 Subpartition by list:語法與list 分割槽完全相同，只不過把關鍵字partition 換成subpartition
 Subpartition by hash:語法與hash 分割槽完全相同，只不過把關鍵字partition 換成subpartition
partition: 分割槽名稱;
range_partition_values_clause:與range 分割槽範圍值的語法;
tablespace_clause:分割槽的儲存屬性，例如所在表空間等屬性(可為空)，預設繼承基表所在表空間的

例子:

create table t5 (id number, createdate date, category varchar2(10))
partition by range(id) subpartition by hash(category)
subpartition 4 store in (tbs01,tbs02,tbs03,tbs04)
      (
        partiton p1 values less than(10) tablespace tbs01,
        partiton p2 values less than(50) tablespace tbs02,
        partiton p3 values less than(100) tablespace tbs03,
        partiton p4 values less than(maxvalue) tablespace tbs04
      ) ;

備註: 可以透過以下檢視查詢相關分割槽及子分割槽資訊.
user_part_tables
user_tab_partitions
user_tab_subpartitions

對於某個分割槽建立hash子分割槽:

create table t6 (id number, createdate date, category varchar2(10))
partition by range(id) subpartition by hash(category)
      (
        partiton p1 values less than(10) tablespace tbs01,
        partiton p2 values less than(50) tablespace tbs02
          (subpartition p2_h1 tablespace tbs01,
           subpartition p2_h2 tablespace tbs02,
           subpartition p2_h3 tablespace tbs03 ),
        partiton p3 values less than(100) tablespace tbs03,
        partiton p4 values less than(maxvalue) tablespace tbs04
      ) ;

可以給各個分割槽指定不同的子分割槽：

create table t7 (id number, createdate date, category varchar2(10))
partition by range(id) subpartition by hash(category)
      (
        partiton p1 values less than(10) tablespace tbs01
          (subpartition p1_h1 tablespace tbs01,
           subpartition p1_h2 tablespace tbs02 ),
        partiton p2 values less than(50) tablespace tbs02
           subpartition 3 store in (tbs01,tbs02,tbs03),
        partiton p3 values less than(100) tablespace tbs03,
        partiton p4 values less than(maxvalue) tablespace tbs04
      ) ;

沒有顯式指定子分割槽的分割槽(比如上例的p3,p4)，系統會自動建立一個子分割槽,
子分割槽名稱為 SYS_SUBP+數字組成。

分割槽模板的應用 ---
在指定子分割槽信賴列之後，制定子分割槽的儲存模板，各個分割槽即會按照子分割槽
模板建立子分割槽，例如：

create table t_partition_rh (id number,name varchar2(50))
partition by range(id) subpartition by hash(name)
subpartition template (
    subpartition h1 tablespace tbspart01,
    subpartition h2 tablespace tbspart02,
    subpartition h3 tablespace tbspart03,
    subpartition h4 tablespace tbspart04)(
partition t_r_p1 values less than (10) tablespace tbspart01,
partition t_r_p2 values less than (20) tablespace tbspart02,
partition t_r_p3 values less than (30) tablespace tbspart03,
partition t_r_pd values less than (maxvalue) tablespace tbspart04);

查詢以下語句可以看到每個分割槽下都有4個子分割槽。
select partition_name,subpartition_name,tablespace_name
from user_tab_subpartitions where table_name='T_PARTITION_RH';

E. 範圍列表組合分割槽(Range-List Partition)

先按照某個欄位進行範圍分割槽，然後再對其他的列進行List分割槽，得出的List
分割槽叫做子分割槽，語法詳見oracle 文件, 建立方式和Range-Hash分割槽相似. 同樣
範圍列表分割槽也可以使用分割槽模板。

分割槽原則 ---

1)、如果選擇的分割槽不能確保各分割槽內記錄量的基本平均，則這種分割槽方式
有可能是不恰當的。比如對於range 分割槽，假設分了10 個分割槽，而其中一個分割槽
中的記錄數佔總記錄數的90%，其它9 個分割槽只佔總記錄數的10%，則這個分割槽方式
就起不到資料平衡的作用。當然，如果你的目的並不是為了平衡，只是為了區分數
據，ok，對於這種情況，我想說的是，你務必要意識到存在這個問題。

2、對於分割槽的表或索引，其所涉及的所有分割槽，其塊大小必須一致。

分割槽索引 ---

對於索引，可以區分建立的是全域性索引或本地索引：

A. 全域性索引(global index): 全域性索引既可以分割槽(和分割槽表的分割槽不一樣)，也可以
不分割槽，可以建立Range分割槽，Hash分割槽，既可以建於分割槽表上，也可以建於非分割槽表
上，也就是說，全域性索引是完全獨立的，因此它也需要我們更多的維護操作。
例子：

create table tab(c1 int,c2 varchar2(16),c3 varchar2(64),
c4 int constraint pk_ta primary key (c1))
partition by range(c1)
(partition p1 values less than (10000000),
partition p2 values less than (20000000),
partition p3 values less than (30000000),
partition p4 values less than (maxvalue));

create index idx_tab_c4 on tab(c4) global partition by range(c4)
( partition ip1 values less than(10000),
partition ip2 values less than(20000),
partition ip3 values less than(maxvalue));

B. 本地索引(local index): 分割槽形式與表的分割槽完全相同，依賴列獨立，儲存屬性
也相同。對於本地索引，索引分割槽的維護自動進行，add/drop/split/truncate表的分
區的時候，本地索引會自動維護其索引分割槽 (注意自動維護不代表索引不失效)。
例子：
create index idx_tab_c2 on tab(c2) local (partition p1,partition p2,partition p3,partition p4);
或者 create index idx_tab_c2 on tab(c2) local ;

本地(區域性)索引 local index --

1. 區域性索引一定是分割槽索引，分割槽鍵等同於表的分割槽鍵，分割槽數等同於表的分割槽
說，一句話，區域性索引的分割槽機制和表的分割槽機制一樣。
2. 如果local index的索引列以分割槽鍵開頭，則稱為字首區域性索引。
3. 如果local index的列不是以分割槽鍵開頭(或不包含分割槽鍵列),則稱為非字首索引。
4. 字首和非字首索引都可以支援分割槽剪除，前提是查詢的條件中包含分割槽鍵。
5. Local index支援分割槽內的唯一性，無法支援表上的唯一性，因此如果要用區域性索引去
給表做唯一性約束，則約束中必須要包括分割槽鍵列。即Local partition index 不能建
立在PK或unique上，除非PK或unique 列上包含有分割槽列才可以。
6. 區域性分割槽索引是對單個分割槽的，每個分割槽索引只指向一個表分割槽，全域性索引則不然，
一個分割槽索引能指向n個表分割槽，同時，一個表分割槽，也可能指向n個索引分割槽，對分割槽
表中的某個分割槽做truncate或者move，shrink等，可能會影響到n個全域性索引分割槽，正
因為這點，區域性分割槽索引具有更高的可用性。
7. 點陣圖索引只能為區域性分割槽索引。
8. 區域性索引(Local Index)多應用於資料倉儲環境中。
9. 因為local partition index 是完全和分割槽表的分割槽屬性一樣，索引分割槽的
區域性性(在各自分割槽內進行索引重組，而不是整個表)導致了必須掃描所有分割槽才能
check到要查詢的值所在的索引區域，所以local index不適合OLTP系統。

分割槽剪除測試：
http://space.itpub.net/35489/viewspace-684640
測試發現：只有包含有分割槽鍵作為where條件才能使用到分割槽剪除

全域性索引global index --

1.全域性索引的分割槽鍵和分割槽數和表的分割槽鍵和分割槽數可能都不相同，表和全域性
索引的分割槽機制不一樣。
2.全域性索引可以分割槽，也可以是不分割槽索引，全域性索引必須是字首索引，即全域性
索引的索引列必須是以索引分割槽鍵作為其前幾列。
3. 全域性分割槽索引的索引條目可能指向若干個分割槽，因此，對於全域性分割槽索引，
即使只動，截斷一個分割槽中的資料，都需要rebulid若干個分割槽甚至是整個索引。
4. 全域性分割槽索引只按範圍或者雜湊hash分割槽，hash分割槽是10g以後才支援。
5.oracle9i以後對分割槽表做move或者truncate時可以用update global indexes語
句來同步更新全域性分割槽索引，用消耗一定資源來換取高度的可用性。
6.表用a列作分割槽，索引用b做區域性分割槽索引，若where條件中用b來查詢，那麼oracle
會掃描所有的表和索引的分割槽，成本會比分割槽更高，此時可以考慮用b做全域性分割槽索引。
7.全域性索引多應用於OLTP系統中。

分割槽索引字典 --

DBA_PART_INDEXES 分割槽索引的概要統計資訊，可以得知每個表上有哪些分割槽索引，
分割槽索引的型別(local/global,)
DBA_ind_partitions每個分割槽索引的分割槽級統計資訊
DBA_indexes minus dba_part_indexes，可以得到每個表上有哪些非分割槽索引

索引重建 --

Alter index idx_name rebuild partition index_partition_name [online nologging]
需要對每個分割槽索引做rebuild，重建的時候可以選擇online(不會鎖定表),或者
nologging建立索引的時候不生成日誌，加快速度。

Alter index rebuild idx_name [online nologging]
對非分割槽索引，只能整個index重建

教你如何成為Oracle 10g OCP - 第九章物件管理(2)

相關文章

教你如何成為Oracle 10g OCP - 第九章 物件管理(2)

相關文章

教你如何成為Oracle 10g OCP - 第九章物件管理(2)