【學習筆記】分割槽表和分割槽索引——概念部分（一）

上週看了《[三思筆記]全面學習分割槽表及分割槽索引.pdf》，按學習順序記錄將學習筆記粘出來如下：

當表中的資料量不斷增加，查詢資料的速度就會變慢，程式效能也會降低，這時候或許可以考慮將表分割槽。表進行分割槽後，邏輯上表仍然是一個邏輯表，但是由於進行了分割槽，就可以將每個分割槽分別存放到不同的物理檔案裡（每個分割槽對應一個段，一個或多個段可以存放到一個表空間，而一個或多個物理檔案又可以存放到一個表空間裡，結果就是可能每個分割槽存放到不同的物理文件裡）

一、使用分割槽的優點：

1、增強可用性：如果表的某個分割槽出現故障，表在其他分割槽的資料仍然可用

2、維護方便：如果表的某個分割槽出現故障，需要修復資料，只修復該分割槽即可；

3、可均衡I/O：可以把不同的分割槽影射到磁碟一平衡I/O，改善整個系統效能

4、改善查詢效能：對分割槽物件的查詢可以僅搜尋自己關心的分割槽，提高檢索速度

二、Oracle資料庫分割槽的方法有：

1、範圍分割槽(Range)

2、HASH分割槽(Hash）

3、列表分割槽(List)

4、複合分割槽(Composite)

4.1 範圍+HASH(range-hash)

4.2 範圍+列表(range-list)

三、各種分割槽的特點：

1、 範圍分割槽是根椐分割槽鍵的不同取值範圍來劃分子集的，關鍵字RANGE，VALUES LESS THAN；

2、列表分割槽是根椐分割槽鍵的一些離散的取值來劃分子集的，關鍵字 list，VALUES；

3、雜湊分割槽是應用雜湊演算法將分割槽鍵對應到某個子集中去，關鍵字 hash，PARTITIONS；

4、混合分割槽只能有兩層，第一層是範圍分割槽，第二層可以是列表分割槽或者雜湊分割槽；

四、分割槽表的建立：

1、建立 range 分割槽表
create table t_partition_range(id number,name varchar2(50))
partition by range(id)
(
partition t_range_p1 values less than(10) tablespace test1,
partition t_range_p2 values less than(20) tablespace test2,
partition t_range_p3 values less than(30) tablespace test3,
partition t_range_p4 values less than(maxvalue) tablespace test4
);
注意：分割槽表儲存表空間可選，即可以是同一個表空間也可以是不同表空間；可以寫也可以不寫，如果不顯示寫分割槽儲存表空間，
則表空間預設和表所儲存表空間一致。

user_part_tables:記錄分割槽的表資訊
user_tab_partitions:記錄表的分割槽的資訊

對 range 分割槽表建立 global 或 local 分割槽索引：
（1）建立global索引range分割槽：
create index idx_parti_range_id on t_partition_range(id)
global partition by range(id)(
partition i_range_p1 values less than(10),
partition i_range_p2 values less than(20),
partition i_range_p3 values less than(30),
partition i_range_pmax values less than(maxvalue)
);

建立global索引的分割槽與建立表的分割槽語句格式完全相同，而且其分割槽形式與索引所在表的分割槽形式沒有關聯關係。
另外需要說明的是：對於range型別的分割槽表，不是隻能建立range分割槽的global索引，也可以建立hash分割槽的global索引或其他。
user_part_indexes:記錄分割槽的索引資訊
user_ind_partitions:記錄索引的分割槽資訊

注意：建立 global 索引分割槽和建立分割槽表語法一樣；global 各個索引分割槽所在表空間可以顯示寫（任意指定）也可以不寫，如果不顯示寫分割槽儲存表空間，則表空間預設和表所儲存表空間一致。

（2）建立 local 索引：
drop index idx_parti_range_id;
create index idx_parti_range_id on t_partition_range(id) local;

這個 local 沒有指定每個分割槽索引所在表空間，當然也可以自定義。
create index idx_part_range_id on t_partition_range(id) local(
partition i_range_p1 tablespace test1,
partition i_range_p2 tablespace test2,
partition i_range_p3 tablespace test3,
partition i_range_pmax tablespace test4
);

2、建立 hash 分割槽
hash 分割槽的建立有兩種方式：
（1）直接指定分割槽名，分割槽鎖在表空間等資訊。
（2）只指定分割槽數量，和可供使用的表空間。
建立hash分割槽表：
create table t_partition_hash(id number,name varchar2(50))
partition by hash(id)(
partition t_hash_p1 tablespace test1,
partition t_hash_p2 tablespace test2,
partition t_hash_p3 tablespace test3
);

實現上述效果還可以這樣：
create table t_partition_hash3(id number,name varchar2(50))
partition by hash(id)
partitions 2 store in (test1,test2,test3);

注意：分割槽表個數和儲存的表空間個數不是一一對應的，那麼這個建立 hash 分割槽的結果會是什麼樣的呢？
SQL> select upt.table_name,upt.partitioning_type,upt.partition_count
2 from user_part_tables upt where upt.table_name='T_PARTITION_HASH2';

TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
------------------------------ ----------------- ---------------
T_PARTITION_HASH2 HASH 5
SQL> select utp.table_name,utp.partition_name,utp.tablespace_name from user_tab_partitions utp where utp.table_name='T_PARTITION_HASH3';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ -----------------------
T_PARTITION_HASH2 SYS_P128 TEST1
T_PARTITION_HASH2 SYS_P129 TEST2
T_PARTITION_HASH2 SYS_P130 TEST3
T_PARTITION_HASH2 SYS_P131 TEST1
T_PARTITION_HASH2 SYS_P132 TEST2

結果：建立了5個分割槽，即建立的分割槽個數和指定個數一致。
建立global索引hash分割槽
create index idx_part_hash_id on t_partition_hash2(id)
global partition by hash(id)
partitions 8 store in (test1,test2,test3);
如果建立上面這種 global 索引會出現什麼情況呢？當然也是建立8個分割槽索引。

3、建立list分割槽
create table t_partition_list(id number,name varchar2(50))
partition by list(id)
(
partition t_list_p1 values (1,2,3,4,5,6,7,8,9) tablespace test1,
partition t_list_p2 values (10,11,12,13,14,15,16,17,18,19) tablespace test2,
partition t_list_p3 values (20,21,22,23,24,25,26,27,28,29) tablespace test3,
partition t_list_p4 values (default) tablespace test4
);

4、建立range_hash組合分割槽
（1）為所有分割槽各建立4個hash子分割槽。
create table t_partition_rh(id number,name varchar2(50))
partition by range(id) subpartition by hash(name)
subpartitions 4 store in (test1,test2,test3)
(
partition t_r_p1 values less than(10),
partition t_r_p2 values less than(20),
partition t_r_p3 values less than(30),
partition t_r_p4 values less than(maxvalue)
);
（2）對某個表建立hash子分割槽
create table t_partition_rh(id number,name varchar2(50))
partition by range(id) subpartition by hash(name)
(
partition t_r_p1 values less than (10) tablespace test1,
partition t_r_p2 values less than (20) tablespace test2,
partition t_r_p3 values less than (30) tablespace test3
(
subpartition t_r_p3_h1 tablespace test1,
subpartition t_r_p3_h2 tablespace test2,
subpartition t_r_p3_h3 tablespace test3
),
partition t_r_p4 values less than (40) tablespace test4
);
（3）給各個分割槽指定不同的子分割槽
create table t_partition_rh(id number,name varchar2(50))
partition by range(id) subpartition by hash(name)(
partition t_r_p1 values less than (10),
partition t_r_p2 values less than (20)
(
subpartition t_r_p2_h1,
subpartition t_r_p2_h2
),
partition t_r_p3 values less than (30)
subpartitions 3,
partition t_r_pd values less than (maxvalue)
(
subpartition t_r_p3_h1,
subpartition t_r_p3_h2,
subpartition t_r_p3_h3
)
);
select 由上可知：未顯示指定子分割槽的分割槽，系統會自動建立一個子分割槽。
（4）分割槽模板的應用
oracle提供的分割槽模板，在指定子分割槽信賴列之後，制定子分割槽的儲存模板，各個分割槽即會按照子分割槽模式建立子分割槽，如：
create table t_partition_rh(id number,name varchar2(50))
partition by range(id) subpartition by hash(name)
subpartition template(
subpartition h1,
subpartition h2,
subpartition h3,
subpartition h4)
(
partition t_r_p1 values less than (10),
partition t_r_p2 values less than (20),
partition t_r_p3 values less than (30),
partition t_r_pd values less than (maxvalue)
);

建立複合分割槽 local 索引：
create index idx_part_r1_id on t_partition_rh(id) local;

5、建立 range-list 組合分割槽
range-list 組合分割槽的建立和 range-hash 相似，不再舉例。

公共準則：
1、如果選擇的分割槽不能確保各分割槽內記錄量的平均值，則這種分割槽方式有可能是不恰當的。
2、對於分割槽的表或索引，其所涉及的所有分割槽，其塊大小必須一致。

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24496749/viewspace-1037956/，如需轉載，請註明出處，否則將追究法律責任。