Oracle分區表全局索引添加分區時不會失效

COALESCE PARTITION | SUBPARTITION (hash)
DROP PARTITION | SUBPARTITION
EXCHANGE PARTITION | SUBPARTITION
MERGE PARTITION | SUBPARTITION
MOVE PARTITION | SUBPARTITION
SPLIT PARTITION | SUBPARTITION
TRUNCATE PARTITION | SUBPARTITION
上面所有操作都會引起global索引失效。
其中下面的操作會引起local索引失效。
MERGE PARTITION
MOVE PARTITION
SPLIT PARTITION
COALESCE PARTITION
EXCHANGE PARTITION

此處測試間隔分區和時間分區，增加分區global和local分區均不會失效，刪除分區導致global索引失效
創建測試表
create table FACT_MONTH1
(
  stat_date   DATE,
  name VARCHAR2(6)
)
PARTITION BY RANGE (stat_date)
  INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
  (
           PARTITION P1 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD'))
  );
插入測試數據
insert into FACT_MONTH1TIME values(TO_DATE('2013-11-1', 'YYYY-MM-DD') ,'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-1-1', 'YYYY-MM-DD') ,'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-1-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-2-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-3-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-4-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-5-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-6-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-7-15', 'YYYY-MM-DD'),'ABCDE');
commit;
查看各種信息
select * from FACT_MONTH1;
select * from user_tab_partitions;
select * from user_part_tables;
select * from user_ind_partitions;
select * from user_part_indexes;
創建索引
create index fa_idx_g on FACT_MONTH1(stat_date);
create index fa_idx_local on FACT_MONTH1(name) local;
查看索引信息
select * from user_tab_partitions;
select * from user_part_tables;
select * from user_ind_partitions;
select * from user_part_indexes;
select * from user_indexes;
添加分區:
insert into FACT_MONTH1 values(TO_DATE('2014-9-15', 'YYYY-MM-DD'),'ABCDE');
commit;
查看索引是否可用:
alter session set statistics_level=all ;
select /*+ index(FA_IDX_G)*/ * from FACT_MONTH1 where stat_date=TO_DATE('2014-9-15', 'YYYY-MM-DD');
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));




時間分區測試
CREATE TABLE FACT_MONTH1TIME
(
  stat_date   DATE,
  name VARCHAR2(6)
)
PARTITION BY RANGE (stat_date)
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('2014-1-1', 'yyyy-mm-dd')) ,
  PARTITION p2 VALUES LESS THAN (TO_DATE('2014-2-1', 'yyyy-mm-dd')) ,
  PARTITION p3 VALUES LESS THAN (TO_DATE('2014-3-1', 'yyyy-mm-dd')) ,
  partition p4 values less than (maxvalue)
);
插入測試數據
insert into FACT_MONTH1TIME values(TO_DATE('2013-11-1', 'YYYY-MM-DD') ,'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-1-1', 'YYYY-MM-DD') ,'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-1-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-2-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-3-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-4-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-5-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-6-15', 'YYYY-MM-DD'),'ABCDE');
insert into FACT_MONTH1TIME values(TO_DATE('2014-7-31', 'YYYY-MM-DD'),'ABCDE');
commit;
創建索引
create index fa_idxtime_g on FACT_MONTH1TIME(stat_date);
create index fa_idxtime_local on FACT_MONTH1TIME(name) local;
增加分區
alter table FACT_MONTH1TIME add partition p6 VALUES LESS THAN (TO_DATE('2014-6-1', 'yyyy-mm-dd'));
alter table FACT_MONTH1TIME add partition p8 VALUES LESS THAN (TO_DATE('2014-8-1', 'yyyy-mm-dd'));
alter table FACT_MONTH1TIME drop partition p8;
查看索引是否可用
alter session set statistics_level=all ;
select /*+ index(FA_IDXTIME_G)*/ * from FACT_MONTH1TIME where stat_date=TO_DATE('2014-9-15', 'YYYY-MM-DD');
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));