oracle hash partition雜湊分割槽(一)

  前幾篇文章: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

---參考資料:

---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