分区表相关

1.hash分区表

哈希分区表上的全局索引和本地索引，在新增分区后都会失效，需要重建索引.

-- 1.1创建哈希分区表

Create Table tb_hxl_hash
 (
 Id Number Not Null,
 cnt Number
 )
Partition By Hash(Id)
(
Partition p_hs1,
Partition p_hs2,
Partition p_hs3,
Partition p_hs4
);

-- 1.2 创建全局索引

Create Index idx_tb_hxl_hash_n1 On tb_hxl_hash(Id) Global;

-- 1.3 生成数据

Declare
 Begin
    For i In 1 .. 100000 Loop
    Insert Into Tb_Hxl_Hash Values (i, i);
    End Loop;
  Commit;
  End;

-- 1.4 这个时候查询索引状态为有效

Select status
 From user_indexes
Where INDEX_NAME = 'IDX_TB_HXL_HASH_N1';

STATUS
--------

VALID

-- 1.5 新增分区

hash分区表新增分区后全局索引会失效

Alter Table tb_hxl_hash Add Partition p_hs5;

 

-- 1.6 这个时候查询索引已经失效

Select status
 From user_indexes
 Where INDEX_NAME = 'IDX_TB_HXL_HASH_N1';

STATUS
--------

UNUSABLE

-- 1.7 创建本地索引

drop index idx_tb_hxl_hash_n1;
Create Index idx_tb_hxl_hash_n1 On tb_hxl_hash(Id) Local;

-- 1.8 索引状态这个时候是有效的

Select aa.partition_name,status
  From user_ind_partitions aa
 Where index_name = 'IDX_TB_HXL_HASH_N1';

PARTITION_NAME STATUS
------------------------------ --------

P_HS1 USABLE
P_HS2 USABLE
P_HS3 USABLE
P_HS4 USABLE
P_HS5 USABLE

-- 1.9 新增分区

Alter Table tb_hxl_hash Add Partition p_hs6;

-- 1.10 新增分区后部分索引已经失效

Select aa.partition_name,status
  From user_ind_partitions aa
 Where index_name = 'IDX_TB_HXL_HASH_N1';

PARTITION_NAME STATUS
------------------------------ --------

P_HS1 UNUSABLE
P_HS2 USABLE
P_HS3 USABLE
P_HS4 USABLE
P_HS5 USABLE
P_HS6 UNUSABLE

--　1.11 删除hash分区

SQL> Alter Table hxl.tb_hxl_hash Drop Partition P_HS5;
Alter Table hxl.tb_hxl_hash Drop Partition P_HS5
                                           *
第 1 行出现错误:
ORA-14255: 未按范围, 列表, 组合范围或组合列表方法对表进行分区

说明hash分区表是不能删除分区的.

-- 1.11 合并分区

hash分区的表不能单独删除某个分区,但可以合并分区

ALTER TABLE hxl.tb_hxl_hash
     COALESCE Partition;

该命令执行后,最后一个分区的数据合并到其他分区,同时删除该分区.合并分区后不管是本地索引还是全局索引都会失效(UNUSABLE).

-- 1.12 交换分区

Alter Table tb_hxl_hash Exchange Partition P_HS2
With Table tb_hxl_ex;

交换分区后分区表的分区索引和交换表的索引会失效. 在该例子中p_hs2对应的分区索引和交换表tb_hxl_ex的索引会失效.

2.List分区表

2.1创建表并生成数据

Create Table hxl.tb_hxl_list
(
  Id Number,
  provcode Number
)
Partition By List(provcode)
(
  Partition p_l1 Values(0),
  Partition p_l2 Values(1),
  Partition p_l3 Values(2),
  Partition p_l4 Values(3)
);


Declare
Begin
    For i In 1 .. 100000 Loop
       Insert Into hxl.tb_hxl_list Values(i,round(dbms_random.value(0,3)));
      End Loop;
     Commit;
  End;

2.2 在非分区键上创建全局索引

Create Index hxl.IDX_TB_HXL_LIST_N1
On hxl.tb_hxl_list(Id) Global;

2.3 增加分区

Alter Table hxl.tb_hxl_list
  Add Partition p_l5 Values(4);

新增list分区索引不会失效

SQL> Select status,INDEX_NAME
  2 From dba_indexes a
  3 Where INDEX_NAME = 'IDX_TB_HXL_LIST_N1';

STATUS INDEX_NAME
-------- ------------------------------

VALID IDX_TB_HXL_LIST_N1

2.4 删除list分区

先向新分区写入数据

Insert Into hxl.tb_hxl_list Values(100,4);
Commit;

SQL> Select Count(1)
  2 From hxl.tb_hxl_list Partition (p_l5);

  COUNT(1)
----------

         1

Alter Table hxl.tb_hxl_list
  Drop Partition p_l5 ;

删除list分区,索引失效

SQL> Select status,index_name
  2 From dba_indexes a
  3 Where a.index_name = 'IDX_TB_HXL_LIST_N1';

STATUS INDEX_NAME
-------- ------------------------------

UNUSABLE IDX_TB_HXL_LIST_N1

2.5 创建本地索引

Drop Index hxl.IDX_TB_HXL_LIST_N1;
Create Index hxl.IDX_TB_HXL_LIST_N1
On hxl.tb_hxl_list(Id) Local;

2.6 新增分区

Alter Table hxl.tb_hxl_list
  Add Partition p_l5 Values(4);

索引状态（有效）

SQL> Select index_name,aa.partition_name,status
  2 From dba_ind_partitions aa
  3 Where index_name In( 'IDX_TB_HXL_LIST_N1');

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------

IDX_TB_HXL_LIST_N1 P_L1 USABLE
IDX_TB_HXL_LIST_N1 P_L2 USABLE
IDX_TB_HXL_LIST_N1 P_L3 USABLE
IDX_TB_HXL_LIST_N1 P_L4 USABLE
IDX_TB_HXL_LIST_N1 P_L5 USABLE

2.7 删除分区

先向新增的分区中写入数据

Insert Into hxl.tb_hxl_list Values(100,4);
Commit;

SQL> Select Count(1)
  2 From hxl.tb_hxl_list Partition (p_l5);

  COUNT(1)
----------

         1

Alter Table hxl.tb_hxl_list
  Drop Partition p_l5 ;

索引状态(有效):

SQL> Select index_name,aa.partition_name,status
  2 From dba_ind_partitions aa
  3 Where index_name In( 'IDX_TB_HXL_LIST_N1');

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------

IDX_TB_HXL_LIST_N1 P_L1 USABLE
IDX_TB_HXL_LIST_N1 P_L2 USABLE
IDX_TB_HXL_LIST_N1 P_L3 USABLE
IDX_TB_HXL_LIST_N1 P_L4 USABLE

测试说明list分区表,删除某个分区后，全局索引会失效，而本地索引则不会失效。