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 created
SQL> insert into part_tab1 select rownum from dual connect by rownum <=50;
50 rows inserted
SQL> commit;
Commit complete
SQL> 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                            USABLE
IDX_ID                         P2                            USABLE
IDX_ID                         P3                            USABLE
删除分区表分区1
SQL> alter table part_tab1 truncate partition p1 ;
Table truncated
SQL> 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                             USABLE
IDX_ID                         P2                             USABLE
IDX_ID                         P3                             USABLE
说明对于局部索引，直接TURNCATE分区是不会影响分区索引的！！！
再实验分区交换的情况，步骤如下：
SQL> create table normal_tab1(id int);
Table created
SQL> insert into normal_tab1 select rownum from dual connect by rownum<10;
9 rows inserted
SQL> commit;
Commit complete
SQL> select count(*) from normal_tab1;
  COUNT(*)
----------------------
     9
SQL> create index idx_norm on normal_tab1 (id);
Index created
SQL> alter table part_tab1 exchange partition p1 with table normal_tab1 including indexes;
Table alteredSQL> select count(*) from normal_tab1;
  COUNT(*)
----------------------
     0
SQL> 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                             USABLE
IDX_ID                         P2                             USABLE
IDX_ID                         P3                             USABLE
由此也可见分区索引的交换加上关键字including indexes 后索引是生效的。
SQL> alter table part_tab1 exchange partition p1 with table normal_tab1 ;
Table altered
SQL>  select index_name,partition_name,status from  user_ind_partitions where index_name='IDX_ID';
INDEX_NAME             PARTITION_NAME                  STATUS
------------------------------ ------------------------------ ----------------------------------------------------
IDX_ID                         P1                            UNUSABLE
IDX_ID                         P2                             USABLE
IDX_ID                         P3                             USABLE
说明如果没有加including indexes进行分区交换，该索引立马失效。甚至导致原来的临时表的索引也失效了，也要重建
SQL> select index_name ,status  from user_indexes where index_name='IDX_NORM';
INDEX_NAME                     STATUS
------------------------------ -------------------------------------
IDX_NORM                       UNUSABLE
SQL> alter index idx_norm rebuild;
Index altered
SQL> alter index idx_id rebuild partition p1;
Index altered

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

二、全局索引

SQL> create index idx_id on part_tab1(id);
Index created
SQL> 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 indexes
ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
那删除分区全局索引会失效吗，操作如下，发现仍然是有效的！
SQL>  alter table part_tab1 truncate partition p1 ;
Table truncated
SQL>  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 truncated
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
如果删除掉置换表的索引，可以进行交换吗？
QL> drop index  idx_norm;
Index dropped
发现置换成功了！
SQL> alter table part_tab1 exchange partition p1 with table normal_tab1 including indexes;
Table altered
SQL> 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的分区是空的，那就不会使整个索引失效

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