Exchange交换分区索引

Exchange分区的时候,同时可以交换索引,分区表的索引必须是本地索引.

-- 创建分区表并初始话数据

Create Table tb_hxl_hash
(
 Id Number Not Null,
 cnt Number
)
Partition By Hash(Id)
(
  Partition p_h1,
  Partition p_h2,
  Partition p_h3,
  Partition p_h4,
  Partition p_h5,
  Partition p_h6,
  Partition p_h7,
  Partition p_h8
);


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


Create Unique Index idx_Tb_Hxl_Hash_u1 On Tb_Hxl_Hash(Id);


Create Table Tb_Hxl_Hash_mid
As
Select * From Tb_Hxl_Hash Where 1=2;


Create Unique Index idx_Tb_Hxl_Hash_mid_u1
On Tb_Hxl_Hash_mid(Id);


Create Unique Index idx_Tb_Hxl_Hash_u1 On Tb_Hxl_Hash(Id);

-- 全局索引不能交换分区索引

Alter Table Tb_Hxl_Hash Exchange Partition p_h1
With Table Tb_Hxl_Hash_mid Including Indexes;

                *
ERROR at line 2:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

--创建本地索引

Drop Index idx_Tb_Hxl_Hash_u1;

Create Unique Index idx_Tb_Hxl_Hash_u1 On Tb_Hxl_Hash(Id)

Local;

-- 交换前各自索引大小

 

SQL> Select A.Bytes, a.segment_name

2 From Dba_Segments a

3 Where A.Segment_Name In ('IDX_TB_HXL_HASH__MID_U1')

4 Or (A.Segment_Name = 'IDX_TB_HXL_HASH_U1' And

5 A.Partition_Name = 'P_H1')

6 /



BYTES SEGMENT_NAME

------- ------------------------------

65536 IDX_TB_HXL_HASH__MID_U1

262144 IDX_TB_HXL_HASH_U1

-- 交换本地索引

Alter Table Tb_Hxl_Hash Exchange Partition p_h1

With Table Tb_Hxl_Hash_mid Including Indexes;

--交换后各自索引的大小,从大小改变可以发现两个表之间的索引已经交换

 

SQL> Select A.Bytes, a.segment_name

2 From Dba_Segments a

3 Where A.Segment_Name In ('IDX_TB_HXL_HASH__MID_U1')

4 Or (A.Segment_Name = 'IDX_TB_HXL_HASH_U1' And

5 A.Partition_Name = 'P_H1')

6 /



BYTES SEGMENT_NAME

------- ------------------------------

262144 IDX_TB_HXL_HASH__MID_U1

65536 IDX_TB_HXL_HASH_U1

交换子分区

Alter Table tb_hxl_user_mid
Exchange subpartition P_20110518_P_01
With Table tb_hxl_user_mid_0518;