前几篇文章:http://space.itpub.net/index.php?uid/9240380/action/spacelist/php/1,主要测试
range partition的语法及相关维护;
分区表除了范围分区,还有列表分区,哈希分区,组合分区(是范围,列表,哈希各种组合)及子分区;
先来测试哈希分区

测试情景:

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

---参考资料:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#CJADDEEH

---1,hash partition创建语法1
SQL> create table t_hash_partition(a int,b int)
2 partition by hash(a)
3 (partition p1 tablespace users,
4 partition p2 tablespace tbs_hang
5 );

Table created.

---2,查询hash partition表的分区及所属表空间
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions
here table_name='T_HASH_PARTITION';

TABLE_NAME
------------------------------------------------------------
PARTITION_NAME
------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
T_HASH_PARTITION
P1
USERS

T_HASH_PARTITION
P2
TBS_HANG

TABLE_NAME
------------------------------------------------------------
PARTITION_NAME
------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------

SQL>

--3,hash partition的分区不能变更日志模式
SQL> alter table t_hash_partition modify partition p1 nologging;
alter table t_hash_partition modify partition p1 nologging
*
ERROR at line 1:
ORA-14252: invalid ALTER TABLE MODIFY PARTITION option for a Hash partition

---4,变更hash partition分区的压缩模式
SQL> alter table t_hash_partition modify partition p1 nocompress;

Table altered.

-----5,具体各个分区的表空间及压缩模式
SQL> create table t_hash_partition(a int,b int)
2 partition by hash(a)
3 (partition p1 tablespace users compress nologging,
4 partition p2 tablespace tbs_hang
5 );
(partition p1 tablespace users compress nologging,

---标明hash partition不能指定nologging *
ERROR at line 3:
ORA-14176: this attribute may not be specified for a hash partition

----6,以压缩模式创建表空间
SQL> create table t_hash_partition(a int,b int)
2 partition by hash(a)
3 (partition p1 tablespace users compress,
4 partition p2 tablespace tbs_hang nocompress
5 )
6 /

Table created.

-----7,hash partition创建语法2
SQL> create table t_hash_partition(a int,b int)
2 partition by hash(a)
3 partitions 2 store in (users,tbs_hang) --括号中为各分区所属表空间
4 /

Table created.

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions w
here table_name='T_HASH_PARTITION';

TABLE_NAME
------------------------------------------------------------
PARTITION_NAME
------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
T_HASH_PARTITION
SYS_P21
USERS

T_HASH_PARTITION
SYS_P22
TBS_HANG

TABLE_NAME
------------------------------------------------------------
PARTITION_NAME
------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------

--指定压缩模式创建hash partition
SQL> create table t_hash_partition(a int,b int)
2 partition by hash(a)
3 partitions 2 store in (users,tbs_hang)
4 compress
5 /

Table created.

---如分区数大于指定的表空间数,则采用循环使用空间
SQL> create table t_hash_partition(a int,b int)
2 partition by hash(a)
3 partitions 3 store in (users,tbs_hang) compress;

Table created.

--
SQL> select table_name,partition_name,tablespace_name,compression from user_tab_
partitions where table_name='T_HASH_PARTITION';

TABLE_NAME
------------------------------------------------------------
PARTITION_NAME
------------------------------------------------------------
TABLESPACE_NAME COMPRESSION
------------------------------------------------------------ ----------------
T_HASH_PARTITION
SYS_P25
USERS ENABLED

T_HASH_PARTITION
SYS_P26
TBS_HANG ENABLED

TABLE_NAME
------------------------------------------------------------
PARTITION_NAME
------------------------------------------------------------
TABLESPACE_NAME COMPRESSION
------------------------------------------------------------ ----------------

T_HASH_PARTITION
SYS_P27 --------------------自第三个分区又开始使用指定的第一个表空间users
USERS ENABLED

---指定的表空间数大于分区总数,也不报错,补充:以分区总数创建hash partition,各分区的名由oracle自动创建,且在user_tab_partitions查不到high_value的值
SQL> create table t_hash_partition(a int,b int)
2 partition by hash(a)
3 partitions 2 store in (users,tbs_hang,t_tbs1)
4 compress
5 /

Table created.

---为了提升hash partition性能,oracle推荐分区数为2的幂次方;,即2,4,8...

----插入数据到hash partition
SQL> insert into t_hash_partition select level,level+1 from dual connect by leve
l<3e4;

29999 rows created.

SQL> commit;

Commit complete.

---查询hash partition表记录总数
SQL> select count(a) from t_hash_partition;

COUNT(A)
----------
29999

---查询hash partition分区
SQL> select count(a) from t_hash_partition partition(SYS_P28);

COUNT(A)
----------
15140

---查询hash partition分区,可见hash partition把数据打散平均存储到各个分区中
SQL> select count(a) from t_hash_partition partition(SYS_P29);

COUNT(A)
----------
14859

ITPub博客

oracle hash partition哈希分区(一)

最新文章