oracle _hash partition,深入解析partition-hash分区

依据惯例，先看官网对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 要求非分区, 非聚簇的表

--rangepartition table exchange partition withhashpartition 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.3range-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.4range-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.5hash 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: 未按范围, 组合范围或列表方法对表进行分区