ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g--11gR2分区表汇总二

oracle10g--11gR2分区表汇总二

原创 Linux操作系统 作者:jack22220613 时间:2011-03-18 09:26:48 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/10037372/viewspace-689821/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    357099