分区表相关

hxl

1.hash分区表

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

-- 1.1创建哈希分区表

1. Create Table tb_hxl_hash
   
2.  (
   
3.  Id Number Not Null,
   
4.  cnt Number
   
5.  )
   
6. Partition By Hash(Id)
   
7. (
   
8. Partition p_hs1,
   
9. Partition p_hs2,
   
10. Partition p_hs3,
    
11. Partition p_hs4
    
12. );
    

-- 1.2 创建全局索引

1. Create Index idx_tb_hxl_hash_n1 On tb_hxl_hash(Id) Global;
   

-- 1.3 生成数据

1. Declare
   
2.  Begin
   
3.     For i In 1 .. 100000 Loop
   
4.     Insert Into Tb_Hxl_Hash Values (i, i);
   
5.     End Loop;
   
6.   Commit;
   
7.   End; 

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

1. Select status
   
2.  From user_indexes
   
3. Where INDEX_NAME = 'IDX_TB_HXL_HASH_N1';
   
5. STATUS
   
6. --------
   
7. 
   
8. VALID

-- 1.5 新增分区

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

1. Alter Table tb_hxl_hash Add Partition p_hs5;
   

 

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

1. Select status
   
2.  From user_indexes
   
3.  Where INDEX_NAME = 'IDX_TB_HXL_HASH_N1';
   
5. STATUS
   
6. --------
   
7. 
   
8. UNUSABLE
   

-- 1.7 创建本地索引

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

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

1. Select aa.partition_name,status
   
2.   From user_ind_partitions aa
   
3.  Where index_name = 'IDX_TB_HXL_HASH_N1';
   
4. 
   
5. PARTITION_NAME STATUS
   
6. ------------------------------ --------
   
7. 
   
8. P_HS1 USABLE
   
9. P_HS2 USABLE
   
10. P_HS3 USABLE
    
11. P_HS4 USABLE
    
12. P_HS5 USABLE

-- 1.9 新增分区

1. Alter Table tb_hxl_hash Add Partition p_hs6;

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

1. Select aa.partition_name,status
   
2.   From user_ind_partitions aa
   
3.  Where index_name = 'IDX_TB_HXL_HASH_N1';
   
4. 
   
5. PARTITION_NAME STATUS
   
6. ------------------------------ --------
   
7. 
   
8. P_HS1 UNUSABLE
   
9. P_HS2 USABLE
   
10. P_HS3 USABLE
    
11. P_HS4 USABLE
    
12. P_HS5 USABLE
    
13. P_HS6 UNUSABLE

--　1.11 删除hash分区

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

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

-- 1.11 合并分区

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

1. ALTER TABLE hxl.tb_hxl_hash
   
2.      COALESCE Partition;

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

-- 1.12 交换分区

1. Alter Table tb_hxl_hash Exchange Partition P_HS2
   
2. With Table tb_hxl_ex;

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

2.List分区表

2.1创建表并生成数据

1. Create Table hxl.tb_hxl_list
   
2. (
   
3.   Id Number,
   
4.   provcode Number
   
5. )
   
6. Partition By List(provcode)
   
7. (
   
8.   Partition p_l1 Values(0),
   
9.   Partition p_l2 Values(1),
   
10.   Partition p_l3 Values(2),
    
11.   Partition p_l4 Values(3)
    
12. );
    
13. 
    
14. 
    
15. Declare
    
16. Begin
    
17.     For i In 1 .. 100000 Loop
    
18.        Insert Into hxl.tb_hxl_list Values(i,round(dbms_random.value(0,3)));
    
19.       End Loop;
    
20.      Commit;
    
21.   End;

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

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

2.3 增加分区

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

新增list分区索引不会失效

1. SQL> Select status,INDEX_NAME
   
2.   2 From dba_indexes a
   
3.   3 Where INDEX_NAME = 'IDX_TB_HXL_LIST_N1';
   
4. 
   
5. STATUS INDEX_NAME
   
6. -------- ------------------------------
   
7. 
   
8. VALID IDX_TB_HXL_LIST_N1

2.4 删除list分区

先向新分区写入数据

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

1. SQL> Select Count(1)
   
2.   2 From hxl.tb_hxl_list Partition (p_l5);
   
3. 
   
4.   COUNT(1)
   
5. ----------
   
6. 
   
7.          1

1. Alter Table hxl.tb_hxl_list
   
2.   Drop Partition p_l5 ;

删除list分区,索引失效

1. SQL> Select status,index_name
   
2.   2 From dba_indexes a
   
3.   3 Where a.index_name = 'IDX_TB_HXL_LIST_N1';
   
4. 
   
5. STATUS INDEX_NAME
   
6. -------- ------------------------------
   
7. 
   
8. UNUSABLE IDX_TB_HXL_LIST_N1

2.5 创建本地索引

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

2.6 新增分区

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

索引状态（有效）

1. SQL> Select index_name,aa.partition_name,status
   
2.   2 From dba_ind_partitions aa
   
3.   3 Where index_name In( 'IDX_TB_HXL_LIST_N1');
   
4. 
   
5. INDEX_NAME PARTITION_NAME STATUS
   
6. ------------------------------ ------------------------------ --------
   
7. 
   
8. IDX_TB_HXL_LIST_N1 P_L1 USABLE
   
9. IDX_TB_HXL_LIST_N1 P_L2 USABLE
   
10. IDX_TB_HXL_LIST_N1 P_L3 USABLE
    
11. IDX_TB_HXL_LIST_N1 P_L4 USABLE
    
12. IDX_TB_HXL_LIST_N1 P_L5 USABLE

2.7 删除分区

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

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

1. SQL> Select Count(1)
   
2.   2 From hxl.tb_hxl_list Partition (p_l5);
   
3. 
   
4.   COUNT(1)
   
5. ----------
   
6. 
   
7.          1

1. Alter Table hxl.tb_hxl_list
   
2.   Drop Partition p_l5 ;

索引状态(有效):

1. SQL> Select index_name,aa.partition_name,status
   
2.   2 From dba_ind_partitions aa
   
3.   3 Where index_name In( 'IDX_TB_HXL_LIST_N1');
   
4. 
   
5. INDEX_NAME PARTITION_NAME STATUS
   
6. ------------------------------ ------------------------------ --------
   
7. 
   
8. IDX_TB_HXL_LIST_N1 P_L1 USABLE
   
9. IDX_TB_HXL_LIST_N1 P_L2 USABLE
   
10. IDX_TB_HXL_LIST_N1 P_L3 USABLE
    
11. IDX_TB_HXL_LIST_N1 P_L4 USABLE

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