深入解析partition-hash分区

kevin0490

　　依据惯例，先看官网对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
语法如下：
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: 未按范围, 组合范围或列表方法对表进行分区