【學習筆記】分區表和分區索引——概念部分（一） - langtianya－IT工程師數位筆記本

文章出處

上周看了《[三思筆記]全面學習分區表及分區索引.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、對于分區的表或索引，其所涉及的所有分區，其塊大小必須一致。