深入解析partition-hash分割槽

pwz1688發表於2014-03-12
依據慣例，先看官網對hash partition的解釋
    Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement. It is a  better choice than range partitioning when: 
■ You do not know beforehand how much data maps into a given range
■ The sizes of range partitions would differ quite substantially or would be difficult to balance manually 
■ Range partitioning would cause the data to be undesirably clustered
■ Performance features such as parallel DML, partition pruning, and partition-wise
joins are important
    The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.
1、建立hash partition
語法如下：
深入解析partition-hash分割槽3
語法看起來比range partition複雜，實際要簡單的多。
column: 分割槽依賴列 ( 支援多個，中間以逗號分隔 );
partition: 指定分割槽，有兩種方式：
    直接指定分割槽名，分割槽所在表空間等資訊。
    只指定分割槽數量，和可供使用的表空間。
例：
--建立hash分割槽表
SQL> create table t_partition_hash(id number,name varchar2(20))
  2 partition by hash(id)(
  3 partition t_hash_p1 tablespace tbs01,
  4 partition t_hash_p2 tablespace tbs02,
  5 partition t_hash_p3 tablespace tbs03);
表已建立。
--檢視hash分割槽表分割槽資訊
SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,high_value,tablespace_name from user_tab_partitions
  2* where table_name='T_PARTITION_HASH'
SQL> /
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------------------------------------------------------------------
T_HASH_P1     TBS01
T_HASH_P2     TBS02
T_HASH_P3     TBS03
--指定分割槽數量及表空間，建立相同的hash分割槽表
SQL> drop table t_partition_hash;
表已刪除。

SQL> edit
已寫入 file afiedt.buf
  1 create table t_partition_hash(id number,name varchar2(20))
  2 partition by hash(id)
  3* partitions 3 store in(tbs01,tbs02,tbs03)
SQL> /
表已建立。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P21     TBS01
SYS_P22     TBS02
SYS_P23     TBS03
提示： 這裡分割槽數量和可供使用的表空間數量之間沒有直接對應關係。 分割槽數並不一定要等於表 空間數。
例如：
--指定分割槽數量
SQL> edit
已寫入 file afiedt.buf
  1 create table t_partition_hash(id number,name varchar2(20))
  2 partition by hash(id)
  3* partitions 3 store in(tbs01,tbs02,tbs03,jjjg)
SQL> /
表已建立。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P24     TBS01
SYS_P25     TBS02
SYS_P26     TBS03
--指定分割槽數量>指定表空間數量
SQL> edit
已寫入 file afiedt.buf
  1 create table t_partition_hash(id number,name varchar2(20))
  2 partition by hash(id)
  3* partitions 3 store in(tbs01,tbs02)
SQL> /
表已建立。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P27 TBS01
SYS_P28 TBS02
SYS_P29 TBS01
2、hash分割槽表上建立索引
2.1、建立global分割槽索引
SQL> create index idx_part_hash_id on t_partition_hash(id)
  2 global partition by hash(id)
  3 partitions 3 store in(tbs01,tbs02,tbs03);

索引已建立。
SQL> edit
已寫入 file afiedt.buf
  1 select partition_name,tablespace_name from user_ind_partitions
  2* where index_name='IDX_PART_HASH_ID'
SQL> /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P30     TBS01
SYS_P31     TBS02
SYS_P32     TBS03
2.2、建立local分割槽索引
SQL> drop index idx_part_hash_id;
索引已刪除。
SQL> create index idx_part_hash_id on t_partition_hash(id) local;
索引已建立。
SQL> select partition_name,tablespace_name from user_ind_partitions
  2 where index_name='IDX_PART_HASH_ID';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P27     TBS01
SYS_P28     TBS02
SYS_P29     TBS01
由此可見，hash分割槽的local索引與range 分割槽的local索引一樣，其local 索引的分割槽完全繼承表的分割槽的屬性。
綜上提示：
1、對於 global 索引分割槽而言，在 10g 中只能支援 range 分割槽和 hash 分割槽。
2、對於 local 索引分割槽而言，其分割槽形式完全依賴於索引所在表的分割槽形式。
3、注意，在建立索引時如果不顯式指定 global 或 local ，則預設是 global 。
4、注意，在建立 global 索引時如果不顯式指定分割槽子句，則預設不分割槽 。
3、分割槽表的管理
3.1增加表分割槽（add partition）
語法：alter table tbname add partition ptname……
例：
SQL> alter table t_partition_hash add partition t_hash_p4 tablespace tbs03;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P27 TBS01
SYS_P28 TBS02
SYS_P29 TBS01
T_HASH_P4 TBS03
注意： 
1 、對於 hash 分割槽，當你執行 add partition 操作的時候， oracle 會自動選擇一個分割槽，並重新分配部分
記錄到新建的分割槽，這也意味著有可能帶來一些 IO 操作。
2 、執行 alter table 時未指定 update indexes 子句：
如果是 range/list 分割槽，其 local 索引和 global 索引不會受影響 ;
如果是 hash 分割槽，新加分割槽及有資料移動的分割槽的 local 索引和 glocal 索引會被置為 unuseable ，需要重新編譯。
3.2、收縮表分割槽（coalesce partitions）
Coalesce partition 僅能被應用於 hash 分割槽或複合分割槽的 hash 子分割槽，執行 之後，會自動收縮當前的表分割槽，一次只能減少一個分割槽，
不能指定減少partitoin的名稱，當表只剩一個分割槽時，再執行coalesce patition會報錯。此功能相當於range 和ist分割槽表的merge partition
例：
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P27 TBS01
SYS_P28 TBS02
SYS_P29 TBS01
T_HASH_P4 TBS03
SQL> alter table t_partition_hash coalesce partition sys_p27;
alter table t_partition_hash coalesce partition sys_p27
                                                *
第 1 行出現錯誤:
ORA-14174: 僅 可以跟在 COALESCE PARTITION|SUBPARTITION 之後
SQL> alter table t_partition_hash coalesce partition;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P27 TBS01
SYS_P28 TBS02
SYS_P29 TBS01
--再執行一次coalesce partition
SQL> alter table t_partition_hash coalesce partition;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P27 TBS01
SYS_P28 TBS02
注意，收縮的只是分割槽，並不會影響到資料，但是視被收縮分割槽中資料的多少，收縮表分割槽也會涉及 到 IO 操作。 另外如果你在執行該語句時沒有指定 update indexes 子句，收縮過程中有資料改動的分割槽其 local 索引 和 glocal 索引都會失效，需要重新編譯。
3.3、交換表分割槽（Exchange partition）
上一章節講range分割槽中提到exchange partition，其實就是資料遷移。hash partition可以與非分割槽表及range分割槽表（注意必須是range的組合分割槽range-hash，而且必須是range-hash與hash進行交換，即alter table tb_partiotn_range_hash exchange partition ptname with table tb_partition_hash，後面會有例項詳細演示)進行exchange partition。
3.3.1 range partition與hash partition相互exchange partition（不支援）
--hash partition insert 
SQL> insert into t_partition_hash values(1,'a');
已建立 1 行。
SQL> insert into t_partition_hash values(11,'b');
已建立 1 行。
SQL> insert into t_partition_hash values(21,'c');
已建立 1 行。
SQL> commit;
提交完成。
SQL> select * from t_partition_hash;
        ID NAME
---------- --------------------
        11 b
         1 a
        21 c
SQL> select * from t_partition_hash partition(t_hash_p1);
        ID NAME
---------- --------------------
        11 b
SQL> select * from t_partition_hash partition(t_hash_p2);
        ID NAME
---------- --------------------
         1 a
SQL> select * from t_partition_hash partition(t_hash_p3);
        ID NAME
---------- --------------------
        21 c
通過以上insert 語句可以看出，hash partition表中分割槽內的資料儲存無規律，人為無法識別新插入的資料將存放於哪個分割槽。
--range partition insert 
SQL> insert into t_partition_range values(11,'a');
已建立 1 行。
SQL> insert into t_partition_range values(21,'b');
已建立 1 行。
SQL> commit;
--hash partition table exchange partition with range partition table
SQL> alter table t_partition_hash exchange partition t_hash_p1
  2 with table t_partition_range;
with table t_partition_range
           *
第 2 行出現錯誤:
ORA-14095: ALTER TABLE EXCHANGE 要求非分割槽, 非聚簇的表

--range partition table exchange partition with  hash partition table
SQL> alter table t_partition_range exchange partition p2
  2 with table t_partition_hash;
with table t_partition_hash
           *
第 2 行出現錯誤:
ORA-14095: ALTER TABLE EXCHANGE 要求非分割槽, 非聚簇的表
結論：由此可見hash分割槽表與range分割槽表無法進行exchange partition操作

3.3.2 range-hash partition exchange partition hash partition(支援，反過來交換則不支援）
--建立range-hash表
SQL> create table t_partition_range_hash(id number,name varchar2(20))
  2 partition by range(id) subpartition by hash(name)
  3 subpartitions 3 store in(tbs01,tbs02,tbs03)(
  4 partition t_range_p1 values less than(10) tablespace tbs01,
  5 partition t_range_p2 values less than(20) tablespace tbs02,
  6 partition t_range_p3 values less than(30) tablespace tbs03,
  7 partition t_range_pmax values less than(maxvalue) tablespace tbs03);
表已建立。
SQL> edit
已寫入 file afiedt.buf
  1 alter table t_partition_range_hash exchange partition t_range_p2
  2* with table t_partition_hash
SQL> /
alter table t_partition_range_hash exchange partition t_range_p2
*
第 1 行出現錯誤:
ORA-14295: 分割槽列和子分割槽列之間的列的型別或大小不匹配
注意：range-hash分割槽表與hash分割槽表進行exchange partition操作時，hash分割槽依賴欄位和型別必須一致，上例中報錯是因為
t_partition_range_hash表是根據name進行subpartition的hash分割槽，但t_partition_hash表是根據id進行hash分割槽。所以會報上面的
ora-14295錯誤。
--建立根據name進行hash分割槽的t_partition_hash_tmp表，如下：
SQL> create table t_partition_hash_tmp(id number,name varchar2(20))
  2 partition by hash(name)
  3 partitions 3 store in(tbs01,tbs02,tbs03);
表已建立。
SQL> insert into t_partition_hash_tmp values(1,'a');
已建立 1 行。
SQL> insert into t_partition_hash_tmp values(12,'b');
已建立 1 行。
SQL> commit;
提交完成。
--執行range-hash exchange partition with hash 
SQL> edit
已寫入 file afiedt.buf
  1 alter table t_partition_range_hash exchange partition t_range_p2
  2* with table t_partition_hash_tmp
SQL> /
with table t_partition_hash_tmp
           *
第 2 行出現錯誤:
ORA-14099: 未對指定分割槽限定表中的所有行

注意：因為t_partition_hash_tmp表中資料id=1的記錄，不在t_partition_range_hash中t_range_p2分割槽範圍內，所以會報錯，如上節講到的
，可利用without validation強制執行交換。如下所示：

SQL> edit
已寫入 file afiedt.buf
  1 alter table t_partition_range_hash exchange partition t_range_p2
  2* with table t_partition_hash_tmp without validation
SQL> /
表已更改。
--查詢交換後的 t_partition_range_hash表資料
SQL> select * from t_partition_range_hash;
        ID NAME
---------- --------------------
         1 a
        12 b
3.3.3 range-hash partition與range partition進行exchange partition(不支援）
SQL> edit
已寫入 file afiedt.buf
  1 alter table t_partition_range exchange partition p2
  2* with table t_partition_range_hash
SQL> /
with table t_partition_range_hash
           *
第 2 行出現錯誤:
ORA-14095: ALTER TABLE EXCHANGE 要求非分割槽, 非聚簇的表
3.3.4 range-hash partition與非分割槽表進行exchange partition(不支援）
SQL> alter table t_partition_range_hash exchange partition t_range_p2
  2 with table t_partition_range_tmp;
with table t_partition_range_tmp
           *
第 2 行出現錯誤:
ORA-14291: 不能用非分割槽表 EXCHANGE 組合分割槽
SQL> select * from t_partition_hash;
        ID NAME
---------- --------------------
        11 b
SQL> insert into t_partition_hash values(1,'a');
已建立 1 行。
SQL> insert into t_partition_hash values(31,'c');
已建立 1 行。
SQL> commit;
提交完成。
3.3.5 hash partition exchange partition with range-hash partition（不支援)
SQL> alter table t_partition_hash exchange partition t_hash_p1
  2 with table t_partition_range_hash;
with table t_partition_range_hash
           *
第 2 行出現錯誤:
ORA-14095: ALTER TABLE EXCHANGE 要求非分割槽, 非聚簇的表
3.4、截斷表分割槽（Truncate partition）
同上一篇range分割槽的truncate partition用法功能一樣。
語法簡單：alter table tbname truncate partition/subpartition ptname;
例：
SQL> select * from t_partition_hash;
        ID NAME
---------- --------------------
        11 b
        12 c
         1 a
SQL> select * from t_partition_hash partition(t_hash_p1);
        ID NAME
---------- --------------------
        11 b
SQL> alter table t_partition_hash truncate partition t_hash_p1;
表被截斷。
SQL> select * from t_partition_hash partition(t_hash_p1);
未選定行
3.5、移動表分割槽（Move Partition）
同上一篇range分割槽的move partition用法功能一樣，主要用來修改表分割槽所在的表空間。
語法：alter table tbname move partition/subpartition ptname……。
例：
SQL> EDIT
已寫入 file afiedt.buf
  1 select partition_name,tablespace_name from user_tab_partitions
  2* where table_name='T_PARTITION_HASH'
SQL> /
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_HASH_P1 TBS01
T_HASH_P2 TBS02
T_HASH_P3 TBS03
SQL> alter table t_partition_hash move partition t_hash_p1 tablespace jjjg;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_HASH_P1 JJJG
T_HASH_P2 TBS02
T_HASH_P3 TBS03
3.6、重新命名錶分割槽（Rename Partition)
語法：alter table tbname rename partition ptname to newptname;
語法和用法都簡單，上一篇也講到過，在此不多說。直接看例子：
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_HASH_P1 JJJG
T_HASH_P2 TBS02
T_HASH_P3 TBS03
SQL> alter table t_partition_hash rename partition t_hash_p1 to t_hash_p1_new;
表已更改。
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_HASH_P1_NEW JJJG
T_HASH_P2 TBS02
T_HASH_P3 TBS03
3.7、hash分割槽表無法進行drop、merge、split分割槽操作
與range分割槽不同，hash分割槽不能進行drop partition、merge partition（hash分割槽可用coalesce partition代替）、split partition（hash分割槽可用add partition代替）操作。詳見下面例子
SQL> select partition_name,tablespace_name from user_tab_partitions
  2 where table_name='T_PARTITION_HASH';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_HASH_P1_NEW JJJG
T_HASH_P2 TBS02
T_HASH_P3 TBS03
--測試執行drop partition
  1* alter table t_partition_hash drop partition t_hash_p1_new
SQL> /
alter table t_partition_hash drop partition t_hash_p1_new
                                            *
第 1 行出現錯誤:
ORA-14255: 未按範圍, 組合範圍或列表方法對錶進行分割槽
--測試執行merge partition
SQL> alter table t_partition_hash merge partitions t_hash_p2,t_hash_p3 into partition t_has
alter table t_partition_hash merge partitions t_hash_p2,t_hash_p3 into partition t_hash_new
            *
第 1 行出現錯誤:
ORA-14255: 未按範圍, 組合範圍或列表方法對錶進行分割槽
--測試執行split partition
SQL> edit
已寫入 file afiedt.buf
  1 alter table t_partition_hash split partition t_hash_p2 at(20) into(
  2 partition t_hash_p2_1 tablespace tbs01,
  3* partition t_hash_p2_2 tablespace tbs02)
SQL> /
alter table t_partition_hash split partition t_hash_p2 at(20) into(
            *
第 1 行出現錯誤:
ORA-14255: 未按範圍, 組合範圍或列表方法對錶進行分割槽

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

相關文章