oracle10g--11gR2分区表汇总二
3.2.5 有normal索引(分区表有本地索引，非分区表有索引)
紧接接上试验过程：
SQL> create index ind_t_no_par on t_no_par(id);
索引已创建。
--在非分区表中创建index，与分区表index所作用的字段相一致
--如果有兴趣，也可以尝试一下分区表与非分区表索引字段不一致的情况，在此不在赘述，在本小节最后，会给出结论
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 UNUSABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
--交换前，分区表index状态
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
--交换前，非分区表index状态
SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par including indexes;
表已更改。
---做交换，指定including indexes,交换分区1
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 UNUSABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
---交换分区1之后，index仍有效
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
---交换分区1之后，index仍有效
SQL> select * from t_no_par;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月 -11 2 20 2 b
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par;
表已更改。
--再次交换分区1，此时不加including indexes
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 UNUSABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 UNUSABLE
--交换完后，被交换分区index失效
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL UNUSABLE
--交换完后，非分区表index失效
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
--在分区表、非分区表索引均失效的情况下，交换分区0
表已更改。
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 UNUSABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 UNUSABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL UNUSABLE
--交换之后，仍都失效
SQL> select * from t_no_par;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月 -11 2 20 2 b
SQL> alter index ind_t_no_par rebuild;
索引已更改。
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 UNUSABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 UNUSABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
--rebuild之后，非分区表index有效
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
表已更改。
--再次交换分区0
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 USABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 UNUSABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL UNUSABLE
--交换完后，此时非分区表index无效，而分区表中交换分区0索引已经有效，可见在做交--换的过程中，如果加入了including index选项，也会将index的状态一同做交换
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
01-4月 -11 2 20 2 b
SQL> select * from t_no_par;
未选定行
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
表已更改。
--再次交换分区0之后
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 UNUSABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 UNUSABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
--又导致index状态互换
SQL> alter index rebuild partition p_t_par_range_0;
alter index rebuild partition p_t_par_range_0
*
第 1 行出现错误:
ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 选项无效
SQL> alter index ind_t_par_range rebuild partition p_t_par_range_0;
索引已更改。
SQL> alter index ind_t_par_range rebuild partition p_t_par_range_1;
索引已更改。
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 USABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
SQL> select * from t_no_par;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月 -11 2 20 2 b
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
表已更改。
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 USABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
SQL> select * from t_no_par;
未选定行
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
01-4月 -11 2 20 2 b
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par ;
表已更改。
--没有including indexes
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 UNUSABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL UNUSABLE
SQL> alter index ind_t_par_range rebuild partition p_t_par_range_0;
索引已更改。
SQL> alter index ind_t_no_par rebuild;
索引已更改。
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 USABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
SQL>
SQL> select * from t_no_par;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月 -11 2 20 2 b
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
表已更改。
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 USABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
--经再次验证，结论没有问题
SQL> select * from t_no_par;
未选定行
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
01-4月 -11 2 20 2 b
SQL> drop index ind_t_no_par;
索引已删除。
SQL> drop index ind_t_par_range;
索引已删除。
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
未选定行
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE TABLE_NAME='T_NO_PAR';
未选定行
给出本小节刚开始提出的话题的结论，即如果分区表与非分区表都有normal index，但index字段不一致的情况下，如果exchange 分区时没有指定including indexes，是可以正常交换的，但如果指定了including indexes，就会报错，这个也很好理解，一旦指定including indexes，则会导致index的交换，如果index不一致，就肯定就出问题了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10037372/viewspace-689821/ ，如需转载，请注明出处，否则将追究法律责任。
