Oracle 分区表索引

一、分区表索引实验

SQL>  create table part_tab1 (id int)        partition by range (id)        (        partition p1 values less than (10),        partition p2 values less than (20),        partition p3 values less than (maxvalue)        )    ;Table createdSQL> insert into part_tab1 select rownum from dual connect by rownum <=50;50 rows insertedSQL> commit;Commit completeSQL> create  index idx_id on part_tab1(id) local;Index created查看索引状态，都是有效果的SQL> select index_name,partition_name,status from  user_ind_partitions where index_name='IDX_ID';INDEX_NAME               PARTITION_NAME                STATUS------------------------------ ------------------------------ --------------------------------------------IDX_ID                         P1                            USABLEIDX_ID                         P2                            USABLEIDX_ID                         P3                            USABLE删除分区表分区1SQL> alter table part_tab1 truncate partition p1 ;Table truncatedSQL> select count(*) from part_tab1 partition(p1);  COUNT(*)-------------------      0查看索引并没有失效SQL>  select index_name,partition_name,status from  user_ind_partitions where index_name='IDX_ID';INDEX_NAME              PARTITION_NAME                 STATUS------------------------------ ------------------------------ ------------------------------------------------IDX_ID                         P1                             USABLEIDX_ID                         P2                             USABLEIDX_ID                         P3                             USABLE说明对于局部索引，直接TURNCATE分区是不会影响分区索引的！！！再实验分区交换的情况，步骤如下：SQL> create table normal_tab1(id int);Table createdSQL> insert into normal_tab1 select rownum from dual connect by rownum<10;9 rows insertedSQL> commit;Commit completeSQL> select count(*) from normal_tab1;  COUNT(*)----------------------     9SQL> create index idx_norm on normal_tab1 (id);Index createdSQL> alter table part_tab1 exchange partition p1 with table normal_tab1 including indexes;Table alteredSQL> select count(*) from normal_tab1;  COUNT(*)----------------------     0SQL> select count(*) from part_tab1 partition(p1);  COUNT(*)--------------------     9分区交换成功后，索引仍然有效！SQL>  select index_name,partition_name,status from  user_ind_partitions where index_name='IDX_ID';INDEX_NAME             PARTITION_NAME                    STATUS------------------------------ ------------------------------ -------------------------------------------------IDX_ID                         P1                             USABLEIDX_ID                         P2                             USABLEIDX_ID                         P3                             USABLE由此也可见分区索引的交换加上关键字including indexes 后索引是生效的。SQL> alter table part_tab1 exchange partition p1 with table normal_tab1 ;Table alteredSQL>  select index_name,partition_name,status from  user_ind_partitions where index_name='IDX_ID';INDEX_NAME             PARTITION_NAME                  STATUS------------------------------ ------------------------------ ----------------------------------------------------IDX_ID                         P1                            UNUSABLEIDX_ID                         P2                             USABLEIDX_ID                         P3                             USABLE说明如果没有加including indexes进行分区交换，该索引立马失效。甚至导致原来的临时表的索引也失效了，也要重建SQL> select index_name ,status  from user_indexes where index_name='IDX_NORM';INDEX_NAME                     STATUS------------------------------ -------------------------------------IDX_NORM                       UNUSABLESQL> alter index idx_norm rebuild;Index alteredSQL> alter index idx_id rebuild partition p1;Index altered

总结：局部索引truncate分区不会导致索引失效。但是如果交换分区的时候，交换的临时表没有索引，或者有索引，没有用including indexes的关键字，会导致局部的索引失效。

二、全局索引

SQL> create index idx_id on part_tab1(id);Index createdSQL> select index_name,table_name,status from user_indexes where table_name='PART_TAB1';INDEX_NAME                 TABLE_NAME                        STATUS------------------------------ ------------------------------ ---------------------------------------------------------IDX_ID                         PART_TAB1                          VALID发现全局索引和准备置换的索引根本不能匹配，如果想置换索引，分区表必须是LOCAL索引！SQL>  alter table part_tab1 exchange partition p1 with table normal_tab1 including indexes;alter table part_tab1 exchange partition p1 with table normal_tab1 including indexesORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配那删除分区全局索引会失效吗，操作如下，发现仍然是有效的！SQL>  alter table part_tab1 truncate partition p1 ;Table truncatedSQL>  select index_name,table_name,status from user_indexes where table_name='PART_TAB1';INDEX_NAME                  TABLE_NAME                       STATUS------------------------------ ------------------------------ ---------------------------------------------------------IDX_ID                         PART_TAB1                          VALID其实有的时候实验出来的结果也要推敲一下，其实删除分区全局索引是会导致索引失效的，只不过刚才TRUNCATE的恰好是一个空分区，所以没有印象，继续操作如下， truncate另一分区就可以知道了SQL>  alter table part_tab1 truncate partition p2;Table truncatedSQL> select index_name,table_name,status from user_indexes where table_name='PART_TAB1';INDEX_NAME                     TABLE_NAME                     STATUS------------------------------ ------------------------------ ---------------------------------------------------------IDX_ID                         PART_TAB1                     UNUSABLE如果删除掉置换表的索引，可以进行交换吗？QL> drop index  idx_norm;Index dropped发现置换成功了！SQL> alter table part_tab1 exchange partition p1 with table normal_tab1 including indexes;Table alteredSQL> select count(*) from part_tab1 partition(p1);  COUNT(*)---------------------     9此时查看索引，发现全局索引也失效了！SQL> select index_name,table_name,status from user_indexes where table_name='PART_TAB1';INDEX_NAME                  TABLE_NAME                       STATUS------------------------------ ------------------------------ ---------------------------------------------------------IDX_ID                         PART_TAB1                        UNUSABLE

全局索引如果置换的普通表有索引，置换根本不成功，除非把置换表索引删除，但是一旦这样置换成功，全局索引也就失效了。truncate分区会使全局索引失效，但是如果truncate的分区是空的，那就不会使整个索引失效

本文出自 “无双城” 博客，谢绝转载！