Oracle分區表

標籤：alt   縮小   oca   uil   提升   空間名   ide   sed   記錄   

　　最近因為業務上客戶資料的資料表空間佔用過大，而想通過刪除一部分資料來縮小資料表空間檔案的大小無法實現，故想到使用分區表來實現

分區表分為定界分割(range)、雜湊分割(hash)、列表分區(list)、以及一些組合分區(range-hash、range-list)

以下為各個分區的建立文法：

1、定界分割(range)

　　簡介：

　　　　

　　文法：

1 　　　　CREATE TABLE 表名 (列定義) partition by range(列名)2 　　　　　(3 　　　　　　partition  分區名  values less than (值)，4 　　　　　　。。。。5 　　　　　　partition 分區名 values less than (maxvalue)　　###預設分區6 　　　　　);

 

 

 

2、雜湊分割(hash)

　　簡介：

 

　　文法：

1 　　　　CREATE TABLE 表名 (列定義) partition by hash(列名)2 　　　　　(3 　　　　　　partition  分區名  tablespace 資料表空間名,4 　　　　　　。。。。5 　　　　　　partition 分區名  tablespace 資料表空間名6 　　　　　);

View Code

 

 

 

注:指定每個分區的資料表空間，用於分散在各個資料檔案中。

 

3、列表分區(list)

　　簡介：

　　　　需要指定列的值，其分區值必須明確指定，該分區列只能有一個，不能像range或者hash分區那樣同時指定多個列作為分區依賴列，但它的單個分區對應值可以是多個。

　　  註：在列表分區中，必須明確分區列可能存在的值，一旦插入的列值不在分區範圍內，則插入/更新就會失敗，因此通常建議使用列表分區時，要建立一個default分區儲存那些不存在指定範圍內的記錄。

　　文法：

1 　　　　CREATE TABLE 表名 (列定義) partition by list(列名)2 　　　　　(3 　　　　　　partition  分區名 values (值1,值2,值3....),4 　　　　　　。。。。5 　　　　　　partition 分區名  values (default)6 　　　　　);

View Code

 

4、組合分區

　　簡介：

　　　　如果某表按照某列分區之後，仍然較大或其他需求，還可以通過分區內再建子分區的方式將分區再分區，即組合分區的方式。

　　文法：

　　

 

分區表的常見操作：

　　查詢分區表：

　　　　 select table_name,partition_name from user_tab_partitions where table_name = ‘表名‘; 

　　　查詢對應分區表中資料

　　　　 select * from 表名 partition (分區名); 

　　添加分區：

　　　　 alter table 表名 add partition values(值) tablespace 資料表空間名; 　

　　拆分分區(split)：　　

alter table 表名 split partition 分區名 {at | values} (值)  into (partition 新分區名 tablespace 空間名,partition 新分區名 tablespace 空間名....);

　　註：標紅處，列表為range類型，使用at；列表為list類型使用values；

　　合并分區(merge)：

　　　　相鄰的分區可以merge為一個分區，新分區的下邊界為原來邊界值較低的分區，上邊界為原來值較高的分區，原來的局部牽引相應也會合并，全域索引會失效，需要rebuild。

　　　　 alter table 表名 merge partitions 分區名1，分區名2... into partition 新分區名;

　　移動分區(move)

　　　　 alter table 表名 move partition 分區名 tablespace 空間名; 

　　　註：分區移動會自動維護局部分區索引，Oracle不會自動維護全域索引，所以需要重新rebuild分區索引。

 

  　　提升：具體需要rebuild哪些索引，可以通過dba_part_indexs，dba_ind_partitions去判斷

　　　　 select index_name,status from user_indexs where table_name = ‘表名‘; 

　　　　查詢局部索引：

　　　　　　 select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name = ‘索引名‘； 

　　　　　　 select index_owner,index_name,partition_name from dba_ind_partitions where index_name = ‘索引名‘; 

　　刪除分區資料(truncate)

　　　　 alter table 表名 truncate partition (分區名); 

　　  註：turncate相對delete操作快，資料倉儲中的大量資料的批量資料載入可能會有用到；

　　　　截斷分區同樣會自動維護局部分區索引，同時會使全域索引unusable，需要重建。

 

　　刪除分區(drop)

　　　　 alter table 表名 drop partition 分區名; 

　　　　同樣會自動維護局部分區索引，同時會使全域索引unusable，需要重建。

 

 

分區索引

　　分為本地索引(local index) 和 全域索引(global index)，局部索引比全域索引容易管理而全域索引比較快。

本地索引

　　文法：

　　　

1 CREATE INDEX IND_表名 on 表名(列)2     local (3         partition 分區名 tablespace 空間名,4         partition 分區名 tablespace 空間名,5         ....);

View Code

 

　

 

Oracle分區表